|
Clear contents of worksheet before populating
Hi everyone, I need a piece of code that will allow me to
clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks |
Clear contents of worksheet before populating
Tom,
Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks |
Clear contents of worksheet before populating
Of course, the first line should be
ActiveSheet.Cells.ClearContents -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Chip Pearson" wrote in message ... Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks |
Clear contents of worksheet before populating
Hi Chip, In the VB application I'm accessing four separate
worksheets at different times and writing to two others at different times. I want to clear one certain worksheet at the beginning of the code. Is there a way to statically tell the VB code which worksheet to clear? Thanks -----Original Message----- Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks . |
Clear contents of worksheet before populating
Tom,
Sure you can. Instead of ActiveSheet, just specify the appropriate sheet. For example, Worksheets("Sheet2").Cells.ClearContents -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi Chip, In the VB application I'm accessing four separate worksheets at different times and writing to two others at different times. I want to clear one certain worksheet at the beginning of the code. Is there a way to statically tell the VB code which worksheet to clear? Thanks -----Original Message----- Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks . |
Clear contents of worksheet before populating
Thanks Chip, as usual your suggestions work.
You should be getting paid for doing this. -----Original Message----- Tom, Sure you can. Instead of ActiveSheet, just specify the appropriate sheet. For example, Worksheets("Sheet2").Cells.ClearContents -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi Chip, In the VB application I'm accessing four separate worksheets at different times and writing to two others at different times. I want to clear one certain worksheet at the beginning of the code. Is there a way to statically tell the VB code which worksheet to clear? Thanks -----Original Message----- Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks . . |
Clear contents of worksheet before populating
Sheets("Sheet3").Cells.ClearContents
Instead of Activesheet use a sheet name. The sheet don't have to be active Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom" wrote in message ... Hi Chip, In the VB application I'm accessing four separate worksheets at different times and writing to two others at different times. I want to clear one certain worksheet at the beginning of the code. Is there a way to statically tell the VB code which worksheet to clear? Thanks -----Original Message----- Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks . |
Clear contents of worksheet before populating
This may be a coincidence but a coworker just asked me
almost the same question. Can I clear all cells in a worksheet EXCEPT for a specific row such as a header row? -----Original Message----- Sheets("Sheet3").Cells.ClearContents Instead of Activesheet use a sheet name. The sheet don't have to be active Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom" wrote in message ... Hi Chip, In the VB application I'm accessing four separate worksheets at different times and writing to two others at different times. I want to clear one certain worksheet at the beginning of the code. Is there a way to statically tell the VB code which worksheet to clear? Thanks -----Original Message----- Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks . . |
Clear contents of worksheet before populating
Tom,
Try something like the following: With Worksheets("Sheet1") .Range(.Range("A2"), ..UsedRange.SpecialCells(xlCellTypeLastCell)).Clea rContents End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... This may be a coincidence but a coworker just asked me almost the same question. Can I clear all cells in a worksheet EXCEPT for a specific row such as a header row? -----Original Message----- Sheets("Sheet3").Cells.ClearContents Instead of Activesheet use a sheet name. The sheet don't have to be active Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom" wrote in message ... Hi Chip, In the VB application I'm accessing four separate worksheets at different times and writing to two others at different times. I want to clear one certain worksheet at the beginning of the code. Is there a way to statically tell the VB code which worksheet to clear? Thanks -----Original Message----- Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks . . |
Clear contents of worksheet before populating
Hi Chip, I typed it in exactly as you have suggested, but
it had a few problems so I took out the comma after the A2 and the first period before UsedRange. Now it has Run Time error 438, Object does not support this property or method. Any ideas? Tom -----Original Message----- Tom, Try something like the following: With Worksheets("Sheet1") .Range(.Range("A2"), ..UsedRange.SpecialCells (xlCellTypeLastCell)).ClearContents End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... This may be a coincidence but a coworker just asked me almost the same question. Can I clear all cells in a worksheet EXCEPT for a specific row such as a header row? -----Original Message----- Sheets("Sheet3").Cells.ClearContents Instead of Activesheet use a sheet name. The sheet don't have to be active Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom" wrote in message ... Hi Chip, In the VB application I'm accessing four separate worksheets at different times and writing to two others at different times. I want to clear one certain worksheet at the beginning of the code. Is there a way to statically tell the VB code which worksheet to clear? Thanks -----Original Message----- Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks . . . |
Clear contents of worksheet before populating
Tom,
Most likely it is a problem with the line wrapping in the post. Try the following: With Worksheets("Sheet1") .Range(.Range("A2"), .UsedRange. _ SpecialCells(xlCellTypeLastCell)).ClearContents End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi Chip, I typed it in exactly as you have suggested, but it had a few problems so I took out the comma after the A2 and the first period before UsedRange. Now it has Run Time error 438, Object does not support this property or method. Any ideas? Tom -----Original Message----- Tom, Try something like the following: With Worksheets("Sheet1") .Range(.Range("A2"), ..UsedRange.SpecialCells (xlCellTypeLastCell)).ClearContents End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... This may be a coincidence but a coworker just asked me almost the same question. Can I clear all cells in a worksheet EXCEPT for a specific row such as a header row? -----Original Message----- Sheets("Sheet3").Cells.ClearContents Instead of Activesheet use a sheet name. The sheet don't have to be active Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom" wrote in message ... Hi Chip, In the VB application I'm accessing four separate worksheets at different times and writing to two others at different times. I want to clear one certain worksheet at the beginning of the code. Is there a way to statically tell the VB code which worksheet to clear? Thanks -----Original Message----- Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks . . . |
Clear contents of worksheet before populating
With Worksheets("Sheet1")
.Range(.Range("A2"), _ .UsedRange.SpecialCells( _ xlCellTypeLastCell)).ClearContents End With The code has word wrapped in the email. The above should work. -- Regards, Tom Ogilvy "Tom" wrote in message ... Hi Chip, I typed it in exactly as you have suggested, but it had a few problems so I took out the comma after the A2 and the first period before UsedRange. Now it has Run Time error 438, Object does not support this property or method. Any ideas? Tom -----Original Message----- Tom, Try something like the following: With Worksheets("Sheet1") .Range(.Range("A2"), ..UsedRange.SpecialCells (xlCellTypeLastCell)).ClearContents End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... This may be a coincidence but a coworker just asked me almost the same question. Can I clear all cells in a worksheet EXCEPT for a specific row such as a header row? -----Original Message----- Sheets("Sheet3").Cells.ClearContents Instead of Activesheet use a sheet name. The sheet don't have to be active Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom" wrote in message ... Hi Chip, In the VB application I'm accessing four separate worksheets at different times and writing to two others at different times. I want to clear one certain worksheet at the beginning of the code. Is there a way to statically tell the VB code which worksheet to clear? Thanks -----Original Message----- Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks . . . |
Clear contents of worksheet before populating
Chip and Tom, I really appreciate all of your patience
with this but I have yet another problem with the code. I typed it in EXACTLY as posted and when it runs I get a run time error of 1004 - Unable to get the Special Cells property of the Range Class. Do either of you know what this means? I'm running Excel97 on a Win2K machine if that helps. Thanks -----Original Message----- Tom, Most likely it is a problem with the line wrapping in the post. Try the following: With Worksheets("Sheet1") .Range(.Range("A2"), .UsedRange. _ SpecialCells(xlCellTypeLastCell)).ClearContents End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi Chip, I typed it in exactly as you have suggested, but it had a few problems so I took out the comma after the A2 and the first period before UsedRange. Now it has Run Time error 438, Object does not support this property or method. Any ideas? Tom -----Original Message----- Tom, Try something like the following: With Worksheets("Sheet1") .Range(.Range("A2"), ..UsedRange.SpecialCells (xlCellTypeLastCell)).ClearContents End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... This may be a coincidence but a coworker just asked me almost the same question. Can I clear all cells in a worksheet EXCEPT for a specific row such as a header row? -----Original Message----- Sheets("Sheet3").Cells.ClearContents Instead of Activesheet use a sheet name. The sheet don't have to be active Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom" wrote in message news:a80f01c3b837$62673970 ... Hi Chip, In the VB application I'm accessing four separate worksheets at different times and writing to two others at different times. I want to clear one certain worksheet at the beginning of the code. Is there a way to statically tell the VB code which worksheet to clear? Thanks -----Original Message----- Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks . . . . |
Clear contents of worksheet before populating
Man do I feel stupid...I'm really sorry for wasting your
time on the last post. I mistyped the SpecialCells...I put in x1 instead of xl (EX-ONE instead of EX-EL). Thanks again for all of your help and patience, and yes it works fine now. Tom - the idiot. -----Original Message----- Chip and Tom, I really appreciate all of your patience with this but I have yet another problem with the code. I typed it in EXACTLY as posted and when it runs I get a run time error of 1004 - Unable to get the Special Cells property of the Range Class. Do either of you know what this means? I'm running Excel97 on a Win2K machine if that helps. Thanks -----Original Message----- Tom, Most likely it is a problem with the line wrapping in the post. Try the following: With Worksheets("Sheet1") .Range(.Range("A2"), .UsedRange. _ SpecialCells(xlCellTypeLastCell)).ClearContents End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi Chip, I typed it in exactly as you have suggested, but it had a few problems so I took out the comma after the A2 and the first period before UsedRange. Now it has Run Time error 438, Object does not support this property or method. Any ideas? Tom -----Original Message----- Tom, Try something like the following: With Worksheets("Sheet1") .Range(.Range("A2"), ..UsedRange.SpecialCells (xlCellTypeLastCell)).ClearContents End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... This may be a coincidence but a coworker just asked me almost the same question. Can I clear all cells in a worksheet EXCEPT for a specific row such as a header row? -----Original Message----- Sheets("Sheet3").Cells.ClearContents Instead of Activesheet use a sheet name. The sheet don't have to be active Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom" wrote in message news:a80f01c3b837$62673970 ... Hi Chip, In the VB application I'm accessing four separate worksheets at different times and writing to two others at different times. I want to clear one certain worksheet at the beginning of the code. Is there a way to statically tell the VB code which worksheet to clear? Thanks -----Original Message----- Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks . . . . . |
Clear contents of worksheet before populating
If you are running it from the click event of a commandbutton, change the
takefocusonclick property of the commandbutton to false. -- Regards, Tom Ogilvy "Tom" wrote in message ... Chip and Tom, I really appreciate all of your patience with this but I have yet another problem with the code. I typed it in EXACTLY as posted and when it runs I get a run time error of 1004 - Unable to get the Special Cells property of the Range Class. Do either of you know what this means? I'm running Excel97 on a Win2K machine if that helps. Thanks -----Original Message----- Tom, Most likely it is a problem with the line wrapping in the post. Try the following: With Worksheets("Sheet1") .Range(.Range("A2"), .UsedRange. _ SpecialCells(xlCellTypeLastCell)).ClearContents End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi Chip, I typed it in exactly as you have suggested, but it had a few problems so I took out the comma after the A2 and the first period before UsedRange. Now it has Run Time error 438, Object does not support this property or method. Any ideas? Tom -----Original Message----- Tom, Try something like the following: With Worksheets("Sheet1") .Range(.Range("A2"), ..UsedRange.SpecialCells (xlCellTypeLastCell)).ClearContents End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... This may be a coincidence but a coworker just asked me almost the same question. Can I clear all cells in a worksheet EXCEPT for a specific row such as a header row? -----Original Message----- Sheets("Sheet3").Cells.ClearContents Instead of Activesheet use a sheet name. The sheet don't have to be active Tom -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom" wrote in message news:a80f01c3b837$62673970 ... Hi Chip, In the VB application I'm accessing four separate worksheets at different times and writing to two others at different times. I want to clear one certain worksheet at the beginning of the code. Is there a way to statically tell the VB code which worksheet to clear? Thanks -----Original Message----- Tom, Try ActiveSheet.Cells.Clear ' or ActiveSheet.Cells.Clear -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tom" wrote in message ... Hi everyone, I need a piece of code that will allow me to clear the contents of an entire worksheet so I can repopulate it with fresh information. Any suggestions? Thanks . . . . |
| All times are GMT +1. The time now is 02:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com