Delete cells using Excel 2007 VBA

Hi all.

I have an excel 2007 template where the end user clicks a button and it populates A5 through C5 with one set of data. And then it populates G5 through I5 with another set of data. The number of rows varies everytime, A5-C5 may have 10 rows and G5-I5 may have 5 rows.

I want to be able to delete all the rows before adding new data whenever the end user clicks the button. Some users will press the button multiple times to get new data and I don't it to simply paste, I want to clear all the rows and then insert the new data.

Any ideas?

Thank you in advance!
printmediaAsked:
Who is Participating?
 
ScriptAddictConnect With a Mentor Commented:
Perhaps something like

Sub clearsheet()
 Range("A5:I10000").EntireRow.clearcontents
 End Sub
0
 
ScriptAddictCommented:
Well, we need to know a couple of things.  

First is there anything on the sheets below the rows that are inserted?

Second is there anything on the sheets beside the rows that are inserted?

If the answer to both is no, I'd just select all the rows where items are inserted, and clear the cells.
0
 
vguzmanIT ManagerCommented:
Range("A5:C9").Select
Selection.ClearContents
Range("G5:I15").Select
Selection.ClearContents
'add code to copy data
Range("A5").Select
ActiveSheet.Paste
'add code to copy other data
Range("G5").Select
ActiveSheet.Paste
0
 
Moosehead_Commented:
Here's an excel forum I found that may help you out with what you need to do:
VBA Excel Forum
0
 
vguzmanIT ManagerCommented:
if you don't have more data on the spreadsheet, then you can delete from A5 until the last cell with data:
LRowColA = Range("A65536").End(xlUp).Row
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.