Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change which columns are deleted.
The following code worked great to see if a name was in a list, if it did not
then it deleted the column and the one after it. However, the PTB's have added 2 additional columns that now need to be deleted. Basically, if the name is not in the list then delete that column and the next 3. what should I change to this code to make it do that? It would be easier to change this code as opposed to change the way my other modules deal with the page after it is created. Sub ShreveportNameDelete() Dim i As Long Dim lastCol As Long With Sheets("Shreveport") lastCol = .Cells(1, "IV").End(xlToLeft).Column For i = lastCol To 1 Step -1 If Len(Trim(.Cells(1, i))) < 0 Then If Application.CountIf(Workbooks("Employee List for Payroll1").Worksheets("List").Columns(2), .Cells(1, i)) = 0 Then .Columns(i).Delete .Columns(i + 1).Delete End If End If Next End With If Sheets("Shreveport").Cells(5, 1) = "Total" Then Sheets("Shreveport").Delete End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change which columns are deleted.
Change .Columns(i).Delete .Columns(i + 1).Delete to:
..Columns(i).Delete .Columns(i + 3).Delete -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Kevin Porter" wrote: The following code worked great to see if a name was in a list, if it did not then it deleted the column and the one after it. However, the PTB's have added 2 additional columns that now need to be deleted. Basically, if the name is not in the list then delete that column and the next 3. what should I change to this code to make it do that? It would be easier to change this code as opposed to change the way my other modules deal with the page after it is created. Sub ShreveportNameDelete() Dim i As Long Dim lastCol As Long With Sheets("Shreveport") lastCol = .Cells(1, "IV").End(xlToLeft).Column For i = lastCol To 1 Step -1 If Len(Trim(.Cells(1, i))) < 0 Then If Application.CountIf(Workbooks("Employee List for Payroll1").Worksheets("List").Columns(2), .Cells(1, i)) = 0 Then .Columns(i).Delete .Columns(i + 1).Delete End If End If Next End With If Sheets("Shreveport").Cells(5, 1) = "Total" Then Sheets("Shreveport").Delete End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change which columns are deleted.
Thanks for the idea Michael. By using your code and stepping throught it, I
was better able to see what was happening. When I used your code it deleted the 3rd column over as opposed to the next three. So I changed the code to .Columns(i).Delete .Columns(i + 1).Delete .Columns(i + 1).Delete .Columns(i).Delete This deleted the column I was on, then the column next to it, next to it then itself again. I probably could acheive the same thing by telling to delete itself 4 times, but this worked. There is probably a prettier way to do this, but this works. Thanks again "Michael" wrote: Change .Columns(i).Delete .Columns(i + 1).Delete to: .Columns(i).Delete .Columns(i + 3).Delete -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Kevin Porter" wrote: The following code worked great to see if a name was in a list, if it did not then it deleted the column and the one after it. However, the PTB's have added 2 additional columns that now need to be deleted. Basically, if the name is not in the list then delete that column and the next 3. what should I change to this code to make it do that? It would be easier to change this code as opposed to change the way my other modules deal with the page after it is created. Sub ShreveportNameDelete() Dim i As Long Dim lastCol As Long With Sheets("Shreveport") lastCol = .Cells(1, "IV").End(xlToLeft).Column For i = lastCol To 1 Step -1 If Len(Trim(.Cells(1, i))) < 0 Then If Application.CountIf(Workbooks("Employee List for Payroll1").Worksheets("List").Columns(2), .Cells(1, i)) = 0 Then .Columns(i).Delete .Columns(i + 1).Delete End If End If Next End With If Sheets("Shreveport").Cells(5, 1) = "Total" Then Sheets("Shreveport").Delete End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleted columns reset their width to zero | Excel Discussion (Misc queries) | |||
Deleted Columns replaced with formatted columns | Excel Discussion (Misc queries) | |||
Deleted empty row for some columns | Excel Programming | |||
deleted 2 columns by mistake - what do I do | Excel Discussion (Misc queries) | |||
formulas refer to columns that are deleted | Excel Programming |