CODEWITHSUNDEEP
vbaexcel
beeba
beeba

Reputation: 432

Clear specific set of columns in Excel VBA

I can't seem to get the syntax right for this. I want to write a VBA script to clear columns A through H from row 4 until the last cell in the column. I know I can write 

Sheets("BAC").Rows(4 & ":" & Sheets("BAC").Rows.Count).ClearContents
Sheets("JPM").Rows(4 & ":" & Sheets("JPM").Rows.Count).ClearContents
Sheets("CITI").Rows(4 & ":" & Sheets("CITI").Rows.Count).ClearContents

to clear the rows down, but how do I change this to only apply to columns A through H instead of all columns?

Upvotes: 0

Views: 6049

Answers (4)

user4467707
user4467707

Reputation: 75

In case you have multiple worksheets in your book try this:

Sub clear_rows()
Dim Wks As String
Dim i As Integer
Dim last_cell As Long

Application.ScreenUpdating = False
For Each Worksheet In Worksheets ' loop through all worksheets
    Wks = Worksheet.Name    'get the name of the sheet as a string
    If Wks = "BAC" Or Wks = "JPM" Or Wks = "CITI" Then  'check the string against your list (could use array or modify to fit your application)
        Worksheets(Wks).Activate
        For i = 1 To 8      'loop through all columns you want a=1,h=8 and so on
            last_cell = Worksheets(Wks).Cells(Rows.Count, i).End(xlUp).Row 'get the last used cell in the column i
            If Not last_cell < 4 Then Worksheets(Wks).Range(Cells(4, i), Cells(last_cell, i)).Clear 'clear it if the column has more than 3 cells
        Next
    End If
Next
Application.ScreenUpdating = True

End Sub

modify to fit your taste!

Upvotes: 1

PartyHatPanda
PartyHatPanda

Reputation: 724

This will do it for you.

Sub clearRowsAtoH()

Dim i As Integer

For i = 1 To 8

    Sheets("BAC").range(Sheets("BAC").Cells(4, i), Sheets("BAC").Cells(Rows.count, i).End(xlUp)).ClearContents
    Sheets("JPM").range(Sheets("JPM").Cells(4, i), Sheets("JPM").Cells(Rows.count, i).End(xlUp)).ClearContents
    Sheets("CITI").range(Sheets("CITI").Cells(4, i), Sheets("CITI").Cells(Rows.count, i).End(xlUp)).ClearContents
Next i

End Sub

EDIT: It can be much cleaner using 'with' statements.

Sub clearRowsAtoH()

Dim i As Integer

For i = 1 To 8
With Sheets("BAC")
    .range(.Cells(4, i), .Cells(Rows.count, i).End(xlUp)).ClearContents
End With
With Sheets("JPM")
    .range(.Cells(4, i), .Cells(Rows.count, i).End(xlUp)).ClearContents
End With
With Sheets("CITI")
    .range(.Cells(4, i), .Cells(Rows.count, i).End(xlUp)).ClearContents
End With
Next i

End Sub

Upvotes: 2

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3378

Try this, for example

With Sheets("BAC")
    .Range("A4:H" & .Range("A4").End(xlDown).Row).ClearContents
End With

Upvotes: 1

Tom K.
Tom K.

Reputation: 1042

Use

Sheets("BAC").Range("A4:H" & Sheets("BAC").UsedRange.Rows.Count).ClearContents
Sheets("JPM").Range("A4:H" & Sheets("JPM").UsedRange.Rows.Count).ClearContents
Sheets("CITI").Range("A4:H" & Sheets("CITI").UsedRange.Rows.Count).ClearContents

Upvotes: 2

Related Questions