CODEWITHSUNDEEP
excelvba
user7415328
user7415328

Reputation: 1083

vba clear column to last used row?

I am using vba to try and clear a column from row 10 to the last used row.

The problem i have is some of my values have gaps in them like so:

1
2
3

5
6
7

8
9

Here is my code:

Sub Clear2()
ActiveSheet.EnableCalculation = False
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

With Sheets(1)
    .Range("H10:H" & .Range("H10").End(xlDown).Row).ClearContents
    .Range("I10:I" & .Range("I10").End(xlDown).Row).ClearContents
    .Range("J10:J" & .Range("J10").End(xlDown).Row).ClearContents
    .Range("K10:K" & .Range("K10").End(xlDown).Row).ClearContents
    .Range("L10:L" & .Range("L10").End(xlDown).Row).ClearContents
    .Range("M10:M" & .Range("M10").End(xlDown).Row).ClearContents


End With
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.EnableCalculation = True
End Sub

The problem i am getting is my code only clears upto the first blank row, and then doesn't clear anything after like so:

5
6
7

8
9

Please can someone show me the proper way of doing this?

Upvotes: 1

Views: 4198

Answers (2)

user3598756
user3598756

Reputation: 29421

With Sheets("mysheet") '<--| change "mysheet" to your actual sheet name
    Intersect(.Range(.Rows(10), .UsedRange.Rows(.UsedRange.Rows.Count)), .Range("H:M")).ClearContents
End With

it uses

  • Range(range1, range2) notation

    to return a range spanning from range1 to range2 ranges

  • Range("Col1:Col2") notation

    to return a range spanning between from columns Col1 to Col2

  • UsedRange property of Worksheet object

    to return a rectangular range that embraces all actually "used" (i.e., not empty or formatted) cells

so:

  • .Range(.Rows(10), .UsedRange.Rows(.UsedRange.Rows.Count))

    gets all cells from row 10 to last "used" row

  • .Range("H:M")

    gets all cells in column H to M

  • intersecting the two above ranges you get the wanted range to clear contents of

Upvotes: 1

Jean-Pierre Oosthuizen
Jean-Pierre Oosthuizen

Reputation: 2693

This should work.

Option Explicit

Sub Clear2()

    ActiveSheet.EnableCalculation = False
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False

    With Sheets(1)

        .Range("H10:H" & .Cells(Rows.Count, "H").End(xlUp).Row).ClearContents
        .Range("I10:I" & .Cells(Rows.Count, "I").End(xlUp).Row).ClearContents
        .Range("J10:J" & .Cells(Rows.Count, "J").End(xlUp).Row).ClearContents
        .Range("K10:K" & .Cells(Rows.Count, "K").End(xlUp).Row).ClearContents
        .Range("L10:L" & .Cells(Rows.Count, "L").End(xlUp).Row).ClearContents
        .Range("M10:M" & .Cells(Rows.Count, "M").End(xlUp).Row).ClearContents

    End With
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    ActiveSheet.EnableCalculation = True

End Sub

Upvotes: 0

Related Questions