Delete cells using Excel 2007 VBA

Posted on 2012-09-21
Last Modified: 2012-09-21
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!
Question by:printmedia
    LVL 11

    Expert Comment

    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.
    LVL 5

    Expert Comment

    'add code to copy data
    'add code to copy other data
    LVL 11

    Accepted Solution

    Perhaps something like

    Sub clearsheet()
     End Sub
    LVL 1

    Expert Comment

    Here's an excel forum I found that may help you out with what you need to do:
    VBA Excel Forum
    LVL 5

    Expert Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now