Delete certain columns in excel 2007 using vba

Posted on 2011-10-06
Last Modified: 2012-06-27
Hi all.

I have an Excel 2007 template with a button (btnClear) that when clicked I would like it to delete all the data starting in C3, it must delete from column C to column L.

The thing is we won't know how many rows there will be, because it changes everytime. Throughout the macro I've used the code below to know how many rows of data are in the excel sheet. In the code below, I start looking at B3 because that's when the actual data begins to be populated (the first 2 rows are for column headers) and it places "N/A" to any row that is blank, maybe we can include something similar in the delete code to know how many rows to delete in columns C through L?

All the data begins in row 3 of all the columns.

Thank you in advance!

Dim r As Long

With Worksheets("Sheet1")
    r = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("B3:B" & r).Value = "N/A"

End With

Open in new window

Question by:printmedia
    LVL 24

    Expert Comment

    If the amount of data in the columns is not going to be more than that in column A then this will clear the cells. Or did you mean you want them deleted?
    Dim r As Long
    With Worksheets("Sheet1")
        r = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("C3:L" & r).Clear
    End With

    Open in new window


    Author Comment

    I would prefer to delete
    LVL 24

    Accepted Solution

    If you just want to delete the cells

    .Range("C3:L" & r).Delete shift:=xlToLeft

    or entire columns

    .Range("C3:L" & r).EntireColumn.Delete

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    729 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

    18 Experts available now in Live!

    Get 1:1 Help Now