Clear non-blank cells?

[Hai]

Hi all,

Please advise on the best script to clear all non-blank cells, in my case from Cells(7,1) forward. I currently use this:

.Range(Cells(7, Columns.Count), Cells(Rows.Count, 1)).Value = ""

Will this scipt take a lot of memory to execute? Is there any other method?

Tks a lot!

 

[Hai]

Yes BUT risky -- if you have any blanks between say row 7 and the last row of data it's possible it won't work -- ie if row 9 is blank and rows 7,8,10 are not it will clear only rows 7 & 8.

you could try the below to limit amount of cells selected

Range(Cells(7, 1), Cells(7, 1).SpecialCells(xlLastCell)).ClearContents

Thanks so much works perfectly

I can't see any difference between the way excel executes my code and yours but it feels like yours is faster.

 

[DonkeyOte]

Yours would look at rows 7:65536 (assuming you're pre XL07 given you're using rows.count) whereas the above would only go to the last used cell -- so if that's on row 15000 it will only use range 7:15000

EDIT: for clarification: xllastcell does not refer to say IV65536 (pre XL07) but to the last "used" cell on the sheet.

 

[Hai]

Yours would look at rows 7:65536 (assuming you're pre XL07 given you're using rows.count) whereas the above would only go to the last used cell -- so if that's on row 15000 it will only use range 7:15000

EDIT: for clarification: xllastcell does not refer to say IV65536 (pre XL07) but to the last "used" cell on the sheet.

Yep, that's perfect! I was looking for things that check for the last used cell or range, but couldn't express it.

So now I do know that rows.count does look at all the rows whereas xlastcell is just the last used cell.

I have virtually no knowledge of VBA, just learning by doing all of your explanations are just what I need!

Again, Tks a lot!