Loop Until Desired Columns left

[SteveT]

Hello,

I'm using JLGWhiz's solution in posting "Deleting Column Based On Header"
to delete columns other than those correctly titled. - Below -

It works Fine but only when run several times or repeated.

Can someone help me enhance to automatically loop until either desired # of
columns with data or until Column Headers indicated below all that left ?

Dim C As Range
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Set myRng = Range("A1", Cells(1, lc))
For Each C In myRng
If C <> "Doc. Date" And C <> "DocumentN" And C <> "Type" And C <>
"Reference" And C <> "LC amnt" Then
C.EntireColumn.Delete
End If
Next

Many Thanks / Happy Holidays
Steven
all left

 

[Dave Peterson]

When you delete rows, it's easier to start at the bottom and work your way to
the top.

When you delete columns, it's easier to start at the far right and work your way
to the left.

Dim iCol As long
dim LastCol as long

with worksheets("Sheetnamehere")
lastcol = .cells(1,.columns.count).end(xltoleft).column
for icol = lastcol to 1 step -1
select case lcase(.cells(1,icol).value)
case is = "doc. date", "type", "reference", "lc amnt"
'skip it
case else
.columns(icol).delete
end select
next icol
end with

Sometimes "select case" makes it a bit easier to see what's going on.

 

[FSt1]

hi
look pretty streight forward to me. what happens if you don't run it
multiple times?

regards
FSt1

 

[SteveT]

Dave,

Your explanation makes perfect sense and solution works great.

thanks !