Clear a range of cells given 2 Cell addresses

[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

 

[JP Ronse]

Hi Ekareem,

Try ...

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

With kind regards,

JP

 

[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_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,

 

[ekareem]

Great - Thank you very much.
Regards,
EK

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,