Vba to clear content but not header

IraAxa

Board Regular
Joined
Apr 23, 2013
Messages
115
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
What row is the header in?

Change the 2's to one higher than the row containing the headers.
 
Upvote 0
well, the header is on the A1, so i want to delete everything from A2 to below that has data in.
 
Upvote 0
If range between AI1:AO2 any cells are merged then while running the macro the header also will get deleted.
 
Upvote 0
The only reason I can see for your code to clear contents of Row 1 is if the value of LastRow is 1.

Step through the code by pressing F8 while your cursor is inside the macro.
When you get to the line that clearscontents, hover your mouse over the LastRow variable, what is it's value?
 
Upvote 0
I dont know what do you mean by that - but the value has some letters in it, ie, dsa-2014-1105.

yesterday when i tested this, i had formulas in the cells and they havent got deleted, hence when i ran the macro now, it just cleared the formulas as well?!
 
Upvote 0
Run this
Code:
Sub Test()
Dim LastRow As Long
 Sheets("All").Select
 LastRow = Range("J" & Rows.Count).End(xlUp).Row
 MsgBox LastRow

LastRow = Range("AO" & Rows.Count).End(xlUp).Row
 MsgBox LastRow
End Sub

What do the message boxes display?
 
Upvote 0
yesterday when i tested this, i had formulas in the cells and they havent got deleted, hence when i ran the macro now, it just cleared the formulas as well?!

Are you saying that the headers in row 1 are actually formulas?
Is it possible that just by clearing the contents, the result of those formulas changed to return "" ?
 
Upvote 0
no, no formulas in headers, and i fixed the part of not clearing formulas by using SpecialCells(xlCellTypeConstants)

LastRow = Range("J" & Rows.Count).End(xlUp).Row
Range("A2:J" & LastRow).SpecialCells(xlCellTypeConstants).ClearContents
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top