Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete columns based on cell value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete columns based on cell value
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete columns based on cell value
thanks J, that fixed my code
On Jan 31, 5:32 pm, JLGWhiz wrote: Try changing: Set curCell = Cells(1 & n) To: Set curCell = Cells(1, n) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete columns based on cell value
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 wrote: Try changing: Set curCell = Cells(1 & n) To: Set curCell = Cells(1, n) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete columns based on cell value
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" wrote in message ... 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 "Robert H" wrote in message oups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete columns based on cell value
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 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 wrote: Try changing: Set curCell = Cells(1 & n) To: Set curCell = Cells(1, n) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Row Based On Two Columns | Excel Programming | |||
Delete Columns based on Strings | Excel Programming | |||
How can I delete a row based on Columns H and I | Excel Programming | |||
How can I delete a row based on Columns H and I | Excel Programming | |||
Delete Columns based on a condition | Excel Programming |