Vba to clear content but not header

[IraAxa]

Hi there,

i have a vba that clears content from specific cells, hence somehow it also deletes the header as well, how can i avoid so?

This is my code:

Dim LastRow As Long
Sheets("All").Select
LastRow = Range("J" & Rows.Count).End(xlUp).Row
Range("A2:J" & LastRow).ClearContents

Sheets("All").Select
LastRow = Range("AO" & Rows.Count).End(xlUp).Row
Range("AI2:AO" & LastRow).ClearContents

 

[Jonmo1]

Well, that code will NOT clear the contents of row 1 so long as the lastRow Variable has a value greater than 1.
Even if Row1 and Row2 are merged.

There must be some other code that's doing it.

I recommend stepping through the code using F8 and watch Row1 at each step of the macro to see when it actually happens.
This is easier if you have 2 monitors, put the Excel on one monitor and the VBA window on the other.

If you don't have 2 monitors, you'll have to shrink each window so you can see both at the same time.

 

[IraAxa]

but still deletes headers

 

[Jonmo1]

This is all guessing games here now.

PM me if you want to email the file to me (with headers and data in rows below still in tact)

 

[Rick Rothstein]

Hi there,

i have a vba that clears content from specific cells, hence somehow it also deletes the header as well, how can i avoid so?

This is my code:

Dim LastRow As Long
Sheets("All").Select
LastRow = Range("J" & Rows.Count).End(xlUp).Row
If LastRow > 1 Then Range("A2:J" & LastRow).ClearContents

Sheets("All").Select
LastRow = Range("AO" & Rows.Count).End(xlUp).Row
If LastRow > 1 Then Range("AI2:AO" & LastRow).ClearContents

I am not entirely sure what is going on, but see if making the changes I highlighted in red above solves your problem.

 

[IraAxa]

i think i got it i used the offset

Dim LastRow As Long
Sheets("All").Select
LastRow = Range("J" & Rows.Count).End(xlUp).Row
Range("A1:J" & LastRow).Offset (1). ClearContents