clear contents of certain cells and format with dynamic count

[asdfasdf]

I'm needing to clear all contents (or delete entire row) from cells A52:C52 and downwards my problem is that depending on my data I don't know how many rows actually have data in them to delete - and i don't want to crash my computer which has happened when i just did every cell til the end. any ideas?

The other issue I'm having is that I want to format rows- e.g. autofit, and interior colour them - but I want to do this until three is no more data. e.g. if data ends at cell C1 i want the A1:C1 to be yellow and autofit, but when the data set is longer I want to do it for the longer data. i need to do this in vba though.

 

[ravishankar]

Hi
your description is not clear. try the following codes

Sub test()
Dim x As Long
x = Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("sheet2").Range("A52:C" & x).Columns.AutoFit
Sheets("sheet2").Range("A2:C" & x).Interior.ColorIndex = 6
Sheets("sheet2").Range("A52:C" & x).ClearContents
MsgBox "Complete"
End Sub

Ravi

 

[asdfasdf]

umm I'll try to explain a bit better -
the data that I have could sometimes just be in a few columns or sometimes be in lots. So any column that has data in i will want the top cell (A) to be coloured yellow. Since it depends on the data, i somehow need a code which first figures out which columns need to be formatted , which i was told would be dynamic counting although I cant find out how this would work.