Clear Contents of Variable Range(s)

[rrmando]

Hello everyone. Pleasure to be here. I have the following macro to clear contents of ranges:

Range("A3:R2223,V3:AF2223,AH3:AI2223,AM3:AM2223,AP3:AP2223").Select
    Selection.ClearContents
    Range("A3").Select

The problem is that I'm a rookie and the range is variable, so I have to manually change the range. So the last row (2223) gets changed to whatever it happens to be for that month.

My question (finally), is there a way to generate an input box requesting the range end, row 3 will ALWAYS be the first row, and then clear contents based on that? The column references will never change, only the last row is variable. Thank you for your help. Have a great day!

 

[Norie]

Do you really need to ask for the last row?

Could you not use code to get it?

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("A3:R" & LastRow &",V3:AF" & LastRow  &",AH3:AI" & LastRow &",AM3:AM" & LastRow & ",AP3:AP" & LastRow).ClearContents

 

[tusharm]

I would do this slightly differently than Norie simply because IMO the code below is easier to understand and maintain.

Sub useIntersect()
    Dim AllCols As Range, AllRows As Range
    
    With ActiveSheet
    Set AllCols = .Range("A:R,V:AF,AH:AI,AM:AM,AP:AP")
    Set AllRows = .Range("3:" & (.UsedRange.Row + .UsedRange.Rows.Count - 1))
    Application.Intersect(AllCols, AllRows).ClearContents
        End With
    End Sub

Of course, if we remove variables that are used only once, we get

Sub useIntersect2()
    With ActiveSheet
    Application.Intersect(.Range("A:R,V:AF,AH:AI,AM:AM,AP:AP"), _
            .Range("3:" & (.UsedRange.Row + .UsedRange.Rows.Count - 1))) _
        .ClearContents
        End With
    End Sub

Hello everyone. Pleasure to be here. I have the following macro to clear contents of ranges:

Range("A3:R2223,V3:AF2223,AH3:AI2223,AM3:AM2223,AP3:AP2223").Select
    Selection.ClearContents
    Range("A3").Select

The problem is that I'm a rookie and the range is variable, so I have to manually change the range. So the last row (2223) gets changed to whatever it happens to be for that month.

My question (finally), is there a way to generate an input box requesting the range end, row 3 will ALWAYS be the first row, and then clear contents based on that? The column references will never change, only the last row is variable. Thank you for your help. Have a great day!

 

[Norie]

tushar

I was considering another approach perhaps using Union, Resize, Offset etc

But IMO the code I posted is easier to understand than that approach, especially considering the OP has said they're a VBA rookie.

But that's my opinion, and there is, as the say, many ways to skin a cat.

 

[rrmando]

I appreciate the responses, however let me throw a little wrench I forgot to mention. The reason I need to specify the last row is because I have sum, subtotal, etc., etc. formulas below that last row.

In this instance, the formulas run across row 2224, but the formulas do not reside in every cell on that row, they will always reside only on columns R:T and AB:AL.

Having said that, can code be used to locate the last row of data based on one of these columns, and then proceed with selecting the range and clearing content? Can I adapt one of your fine examples to work with this?

Thanks again for your answers. I truly want to thank all of you for sharing your Excel knowledge with us newbies.