for next discontinuous

[Macklin]

I am clearing multiple columns from a userform, but they are not continuous. Is there a way to do this with a for next loop. I am showing an example, but it is really about 20 columns and done in multiple subs, so I would like to make it look better.

ws.Cells(rw, "B").Value = ""
ws.Cells(rw, "D").Value = ""
ws.Cells(rw, "E").Value = ""
ws.Cells(rw, "G").Value = ""
ws.Cells(rw, "H").Value = ""

Thanks for all the help

 

[Norie]

There are various ways you could probably do it but they might be more 'efficient' but they might also not be too pretty.

Can you post the 'real' code or further details?

 

[Macklin]

This is the full list that needs cleared, the code in each sub does something different and then clears the data at the end. This is the full list.

ws.Cells(rw, "B").Value = ""
ws.Cells(rw, "D").Value = ""
ws.Cells(rw, "E").Value = ""
ws.Cells(rw, "G").Value = ""
ws.Cells(rw, "H").Value = ""
ws.Cells(rw, "i").Value = ""
ws.Cells(rw, "J").Value = ""
ws.Cells(rw, "K").Value = ""
ws.Cells(rw, "L").Value = ""
ws.Cells(rw, "N").Value = ""
ws.Cells(rw, "O").Value = ""
ws.Cells(rw, "P").Value = ""
ws.Cells(rw, "Q").Value = ""
ws.Cells(rw, "R").Value = ""
ws.Cells(rw, "S").Value = ""
ws.Cells(rw, "T").Value = ""
ws.Cells(rw, "U").Value = ""
ws.Cells(rw, "V").Value = ""
ws.Cells(rw, "W").Value = ""
ws.Cells(rw, "X").Value = ""
ws.Cells(rw, "Y").Value = ""
ws.Cells(rw, "Z").Value = ""

I was thinking of something like:

For COLM = "B", "D", "E", "G", "H", "I", "J", "K"
ws.Cells(rw, "COLM").Value = ""
NEXT COLM

 

[Rick Rothstein]

Give this single line of code a try...

Intersect(Range("B:B,D:E,G:L,N:Z").EntireColumn, Rows(rw)).ClearContents

 

[Macklin]

Thank you Rick. I am very impressed with the code. It is so simple. I am new to this and really make it harder than it has to be sometimes.

Thanks for teaching me something new

 

[Norie]

If you do want to loop one way you could use something like this.

Dim col As Long
    For col = 2 To 26
        Select Case col
            Case 3, 6, 13    ' C, F, M
                ' do nothing
            Case Else
                ws.Cells(rw, col).Value = ""
        End Select
    Next col

Or sort of using the column letters.

    For col = Asc("B") To Asc("Z")
        Select Case Chr(col)
            Case "C", "F", "M"
                ' do nothing
            Case Else
                ws.Cells(rw, col).Value = ""
        End Select
    Next col

PS What do all the subs do and why do you need to clear the data after each one?

 

[Rick Rothstein]

Thank you Rick. ... It is so simple. I am new to this and really make it harder than it has to be sometimes.

Thanks for teaching me something new

It just occurred to me that the ranges I specified are already entire columns, so I did not need to specify the EntireColumn property call in my code line. This slightly shorter single line of code will also work...

Intersect(Range("B:B,D:E,G:L,N:Z"), Rows(rw)).ClearContents

 

[Macklin]

Thank you both. I wasn't sure how to use CASE and that is what I meant to try originally. The reason for the multiple deletes are: 1) just a clear data button 2) move information to a different row, then delete the old row 3) I have 2 other sheets I populate on a weekly basis and print, then I can remove the data.

I have learned a lot as I go along. I can seem to make things work. It just isn't always the most efficient way to do things. I have a stack of books and I think online help is probably the best thing I have found for questions like this. I really appreciate that there are people like you around. Thanks again