Im trying to loop through each cell in the first row of a sheet and if
the cell contains the text "ID" the delete that entire column. the
following code works well except it starts at the 11th column instead
of at the first also the curCell value is blank after a certain number
of cells even tho there is still data in the location it is looking
at.

Sub DeleteIDCol()
'removes the LC_ID and QD_ID columns.
Dim colHead As Range
Dim n As Integer
Dim curCell As Range

Set colHead = Rows(1)
For n = 1 To colHead.Columns.Count
Set curCell = Cells(1 & n)
If InStr(1, curCell, "ID") > 0 Then
curCell.EntireColumn.Delete
End If
Next n
End Sub

Try changing: Set curCell = Cells(1 & n)
To: Set curCell = Cells(1, n)


"Robert H" wrote:

> Im trying to loop through each cell in the first row of a sheet and if
> the cell contains the text "ID" the delete that entire column. the
> following code works well except it starts at the 11th column instead
> of at the first also the curCell value is blank after a certain number
> of cells even tho there is still data in the location it is looking
> at.
>
> Sub DeleteIDCol()
> 'removes the LC_ID and QD_ID columns.
> Dim colHead As Range
> Dim n As Integer
> Dim curCell As Range
>
> Set colHead = Rows(1)
> For n = 1 To colHead.Columns.Count
> Set curCell = Cells(1 & n)
> If InStr(1, curCell, "ID") > 0 Then
> curCell.EntireColumn.Delete
> End If
> Next n
> End Sub
>
>

First work from the back to the front. Correct for wordwrap

Sub DeleteIDCol_Don()
For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
If InStr(UCase(Cells(1, i)), "ID") > 0 Then Columns(i).Delete
Next
End Sub

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Robert H" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Im trying to loop through each cell in the first row of a sheet and if
> the cell contains the text "ID" the delete that entire column. the
> following code works well except it starts at the 11th column instead
> of at the first also the curCell value is blank after a certain number
> of cells even tho there is still data in the location it is looking
> at.
>
> Sub DeleteIDCol()
> 'removes the LC_ID and QD_ID columns.
> Dim colHead As Range
> Dim n As Integer
> Dim curCell As Range
>
> Set colHead = Rows(1)
> For n = 1 To colHead.Columns.Count
> Set curCell = Cells(1 & n)
> If InStr(1, curCell, "ID") > 0 Then
> curCell.EntireColumn.Delete
> End If
> Next n
> End Sub
>

thanks J, that fixed my code

On Jan 31, 5:32 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> Try changing: Set curCell = Cells(1 & n)
> To: Set curCell = Cells(1, n)

You should take a good look at Don's suggested code. It does the same thing
that you are doing but is more efficient because it only uses the range where
you have data. Your code will chech all 256 cells of each row for each
iteration.

"Robert H" wrote:

> thanks J, that fixed my code
>
> On Jan 31, 5:32 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > Try changing: Set curCell = Cells(1 & n)
> > To: Set curCell = Cells(1, n)
>
>
>

Just to add

If InStr(UCase(Cells(1, i)), "ID") > 0 Then . . .

could be

If InStr(1,Cells(1, i),"ID",vbTextcompare) Then . . .

Easier to use the built in capabilities of Instr. If "ID" is in the string,
the result will be non-zero and thus interpreted as True.

--
Regards,
Tom Ogilvy

"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> First work from the back to the front. Correct for wordwrap
>
> Sub DeleteIDCol_Don()
> For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
> If InStr(UCase(Cells(1, i)), "ID") > 0 Then Columns(i).Delete
> Next
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Robert H" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Im trying to loop through each cell in the first row of a sheet and if
>> the cell contains the text "ID" the delete that entire column. the
>> following code works well except it starts at the 11th column instead
>> of at the first also the curCell value is blank after a certain number
>> of cells even tho there is still data in the location it is looking
>> at.
>>
>> Sub DeleteIDCol()
>> 'removes the LC_ID and QD_ID columns.
>> Dim colHead As Range
>> Dim n As Integer
>> Dim curCell As Range
>>
>> Set colHead = Rows(1)
>> For n = 1 To colHead.Columns.Count
>> Set curCell = Cells(1 & n)
>> If InStr(1, curCell, "ID") > 0 Then
>> curCell.EntireColumn.Delete
>> End If
>> Next n
>> End Sub
>>
>
>

Funny you say that J, that just what I did and for the same
reason . Onece I got my code working with your help I step through
it and realized I was looking at every cell. And tried Dons code

On Jan 31, 10:27 pm, JLGWhiz <JLGW...@discussions.microsoft.com>
wrote:
> You should take a good look at Don's suggested code. It does the same thing
> that you are doing but is more efficient because it only uses the range where
> you have data. Your code will chech all 256 cells of each row for each
> iteration.
>
> "Robert H" wrote:
> > thanks J, that fixed my code
>
> > On Jan 31, 5:32 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > > Try changing: Set curCell = Cells(1 & n)
> > > To: Set curCell = Cells(1, n)