Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear a range of cells given 2 Cell addresses
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear a range of cells given 2 Cell addresses
Hi Ekareem,
Try ... range(cells(r1,c1), cells(r2,c2)).clearcontents. With kind regards, JP "ekareem" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear a range of cells given 2 Cell addresses
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. "JP Ronse" wrote: Hi Ekareem, Try ... range(cells(r1,c1), cells(r2,c2)).clearcontents. With kind regards, JP "ekareem" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear a range of cells given 2 Cell addresses
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, -- Data Hog "ekareem" wrote: 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. "JP Ronse" wrote: Hi Ekareem, Try ... range(cells(r1,c1), cells(r2,c2)).clearcontents. With kind regards, JP "ekareem" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear a range of cells given 2 Cell addresses
Great - Thank you very much.
Regards, EK "J_Knowles" wrote: 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, -- Data Hog "ekareem" wrote: 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. "JP Ronse" wrote: Hi Ekareem, Try ... range(cells(r1,c1), cells(r2,c2)).clearcontents. With kind regards, JP "ekareem" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANGE addresses. Getting from other cells. | Excel Worksheet Functions | |||
If range is empty, clear other cells | Excel Programming | |||
clear range of cells if another becomes blank | Excel Worksheet Functions | |||
Clear range of cells in different worksheet | Excel Programming | |||
Named Range Cells vs. Absolute Cell Addresses | Excel Programming |