VBA Row Delete and Sort

Question

I have two problems that are seemingly impossible to fix. First is a type mismatch that I can't place. (error 13)

The goal is to delete every empty row in the range between blocks of data. Every variable I call (i, y, and rows) are defined. Should i or rows not be longs?

Second is a line telling me I need an object when it was working fine before I tried to solve problem 1 and never even touched it. (error 424)

This one I really can't figure out. I don't know what object it wants me to call if I'm already telling it to select the used range of the active worksheet and sort in ascending order, then descending order by two different values. The sheet has headers and is sorting by time, then date.

Sub Import()

    Dim xSht As Worksheet, xWb As Workbook
    Dim xFileDialog As FileDialog, f, i, rows, r As Long
    Dim xStrPath As String, xFile As String
    Dim y As Range
    
    Dim iCntr
    Dim rng As Range
    Set rng = Range("A10:D20")

    Application.ScreenUpdating = False
   
.
.
.
.
.
        Set y = Range("A1:D2000")


        For i = y.Row + y.rows.Count - 1 To y.Row Step -1
        If Application.WorksheetFunction.CountA(rows(i)) = 0 _
        Then rows(i).EntireRow.Delete
        Next
        
        

    Worksheets("BY SHIFT").Activate

        
    Worksheets("BY SHIFT").Sort.SortFields.Clear
    ActiveWorksheet.UsedRange.Sort Key1:=Range("C1"), Key2:=Range("B1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlDescending

    
    Worksheets("2718").Activate

    
    Application.ScreenUpdating = True

End Sub

Warcupine · Accepted Answer · 2023-01-17T18:38:47.820

Rows() Without an explicit parent object is a function that requires an array, i is a long. You want something like ActiveSheet.Rows(i) which will access that particular row of the sheet object. That is the type mismatch error.

ActiveWorksheet is not a thing, you want ActiveSheet, or better yet explicitly reference a specific sheet. This is your 424 error, Use Option Explicit and you get a compile error that should be clearer, and is good practice.

i = y.Row + y.rows.Count - 1 Evaluates to y.rows.Count You can leave out the math.

I added a With statement to your sorting and removed ActiveSheet references.

See here for why: How to avoid using Select in Excel VBA

Sub Import()
    Dim y As Range
    Dim i As Long

    Application.ScreenUpdating = False
   
    Set y = Range("A1:D2000")

    For i = y.rows.Count To y.Row Step -1
        If Application.WorksheetFunction.CountA(ActiveSheet.rows(i)) = 0 Then
            ActiveSheet.rows(i).EntireRow.Delete
        End If
    Next

    With Worksheets("BY SHIFT")
        .Sort.SortFields.Clear
        .UsedRange.Sort Key1:=.Range("C1"), Key2:=.Range("B1"), Header:=xlYes, _
        Order1:=xlAscending, Order2:=xlDescending
    End With
    
    Worksheets("2718").Activate
    
    Application.ScreenUpdating = True

End Sub

VBA Row Delete and Sort

1 Answers1