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!

 

[DonkeyOte]

presumably you want:

Rows("7:65536").ClearContents

you could even try deleting the rows (8:65536) as the rows would simply be replaced (but turn off displayalerts before delete and reset to true after delete), obviously you would lose formatting if that's an issue.

If you are using XL07 the 65536 may need to be more flexible...

 

[Makrini]

or another way..

Range("A1", Range("A1").End(xlToRight).End(xlDown)).ClearContents

 

[Makrini]

or..

Range("A1").CurrentRegion.ClearContents

 

[DonkeyOte]

Makrini, OP wants 1:6 untouched.

 

[Makrini]

then needs to use "A7" instead

 

[Hai]

presumably you want:

Rows("7:65536").ClearContents

you could even try deleting the rows (8:65536) as the rows would simply be replaced (but turn off displayalerts before delete and reset to true after delete), obviously you would lose formatting if that's an issue.

If you are using XL07 the 65536 may need to be more flexible...

Thanks for the command. Still, defining 7:65536 will enlarge the file to a few MBs.

And .Clear or .ClearContents will take away formatting as well right? I need to keep the formatting.

 

[DonkeyOte]

.ClearContents will not clear formatting whereas a straightforward .Clear would as would deleting the rows altogether (obviously)

 

[Hai]

or another way..

Range("A1", Range("A1").End(xlToRight).End(xlDown)).ClearContents

Is this faster - more efficient - than my original code?

 

[DonkeyOte]

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