With Statement VBA

SmashVinny 0
Viewed 37

I'm trying to use the With statement to make my code cleaner.

But I'm getting an error when the code try to run the ".Row" line, if my Excel file is opened in another sheet then the "Mysheet".

I need to select the entire row, so I can delete everything below it.

With Sheets("Mysheet")
    .Row("12:12").Select
    .Range(Selection, Selection.End(xlDown)).Delete
    .Rows("11:11").ClearContents
with-statement vba
2 Answers

There's no need to Select anything:

With Sheets("Mysheet")
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    .Rows("12:" & lastRow).Delete
    .Rows(11).ClearContents
End With
BigBen0

Clear Data Below

Sub ClearMySheet()
    
    Const wsName As String = "MySheet"
    Const FirstRow As Long = 11
    
    With ThisWorkbook.Worksheets(wsName).UsedRange
        Dim rOffset As Long: rOffset = FirstRow - .Row
        If rOffset < 0 Then Exit Sub ' used range below first row
        
        Dim rCount As Long: rCount = .Rows.Count - rOffset
        If rCount < 1 Then Exit Sub ' used range above first row
        
        With .Resize(rCount).Offset(rOffset)
            .Rows(1).ClearContents
            If rCount = 1 Then Exit Sub ' no data, only first row
            .Resize(rCount - 1).Offset(1).Clear
        End With
    End With
        
End Sub
VBasic20080
Related