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

Delete certain columns in excel 2007 using vba

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

0
printmedia
Asked:
printmedia
  • 2
1 Solution
 
StephenJRCommented:
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

0
 
printmediaAuthor Commented:
I would prefer to delete
0
 
StephenJRCommented:
If you just want to delete the cells

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

or entire columns

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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