Clear a range of cells given 2 Cell addresses

E

ekareem

Hi
I want to be a able to clear a rectangular area.
I know the top most left corner address.
I know the bottom righ corner address.
all addresses are in the format r,c where r,c are integers.
So I know cell 1 to have coordinates (r1,c1) and cell 2 to have coordinates
(r2,c2).

How can I issue a clear command to clear all data and formulas in this region?

Thanks much in advance.

Ekareem
 
J

JP Ronse

Hi Ekareem,

Try ...

range(cells(r1,c1), cells(r2,c2)).clearcontents.

With kind regards,

JP
 
E

ekareem

Hi
That worked real well.

If I may ask another question on the same subject,....What if the second
coordinates were not exactly known, say we want to clear the contents from
point (r1,c1) till the end of the sheet?

Thanks very much.
 
J

J_Knowles

Sub clearContentsMac()
'Clears A1:B2
Range(Cells(1, 1), Cells(2, 2)).ClearContents
End Sub

Sub clearContentsUsedRange()
'Clears all data on activesheet
ActiveSheet.UsedRange.ClearContents
End Sub

Sub clearContentsLastRowCol()
'clears data based on the No of columns in row 1 & No of rows in Col A
Dim lastrow As Long
Dim lastcol As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
' chg "A" to any column with max data to check
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
' chg 1 to any row with max data to check
Range(Cells(1, 1), Cells(lastrow, lastcol)).ClearContents
End Sub

HTH,
 
E

ekareem

Great - Thank you very much.
Regards,
EK

J_Knowles said:
Sub clearContentsMac()
'Clears A1:B2
Range(Cells(1, 1), Cells(2, 2)).ClearContents
End Sub

Sub clearContentsUsedRange()
'Clears all data on activesheet
ActiveSheet.UsedRange.ClearContents
End Sub

Sub clearContentsLastRowCol()
'clears data based on the No of columns in row 1 & No of rows in Col A
Dim lastrow As Long
Dim lastcol As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
' chg "A" to any column with max data to check
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
' chg 1 to any row with max data to check
Range(Cells(1, 1), Cells(lastrow, lastcol)).ClearContents
End Sub

HTH,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top