Simple Macro Question

[wormaldp]

Hi,

I'm pretty much a newbie at macros and so this may or may not be incredibly simple for some of you..

I have some excel spreadsheet where I want to delete all of the data in columns AA:AH apart from the headers.

There are some blanks in the table which is making it a bit tricky.

I tried on the macro recorder - starting at cell z1, pressing Ctl down, (as there are no blanks in column z) and then going right one cell and then selecting everything from there to cell AH2 and then deleting. However, vba records the cell "AA569" as soon as I go right from the bottom of column z, rather than recording the last cell in the column AA. Therefore, when I try using the macro on a different data sheet, this macro does not work.

Please let me know if you have a solution.

Thanks,

 

[hippiehacker]

using the usedrange method

Range("AA2:AH" & ActiveSheet.UsedRange.Rows.Count).ClearContents

 

[JackDanIce]

Welcome to the board. Try:

Sub ClearArea()

Dim i as Long

i = Range("Z" & Rows.Count).End(xlUp).Row
Range("AH2:AA" & i).ClearContents

End Sub

Rows.Count is the total count of rows in a sheet (this value is different in different versions of Excel), so Z & rows.count is cell Z1048576 in Excel 2007 or later. It then 'jumps' up to the first non-empty cell it finds (CTRL+Up) and passes the value of that row to i

 

[Rick Rothstein]

Another way...

Range("AA2:AH" & Rows.Count).ClearContents

 

[wormaldp]

Thanks very much everyone- those all worked nicely.

I get the intuition for the code as well which is good!

 

[WillisWizward]

Hi Pete
I think this will work well

Range("AA2:AH1000000").Delete

ham