• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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!
0
printmedia
Asked:
printmedia
  • 2
  • 2
1 Solution
 
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
 
ScriptAddictCommented:
Perhaps something like

Sub clearsheet()
 Range("A5:I10000").EntireRow.clearcontents
 End Sub
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now