Trying to set range dynamically in VBA / Excel based on dump sheet length

Question

I have an Excel workbook consisting of 8 sheets, one of which is a Dump page I export data to in bulk. I've configured a script (adapted from a helpful guide I found here) to automatically sort that data into the other 7 sheets based on the contents of the D column:

Option Explicit

Sub Autosort()

Dim Cell As Range

With Sheets(8) ' Starting from sheet 8
    For Each Cell In .Range("D1:D" & .Cells(.rows.Count, "D").End(xlUp).Row)
        If Cell.Value = "Laptop" Then
             ' Copy>>Paste in 1-line (no need to use Select)
            .rows(Cell.Row).Copy Destination:=Sheets(2).rows(Cell.Row)
        End If
    Next Cell
    For Each Cell In .Range("D1:D" & .Cells(.rows.Count, "D").End(xlUp).Row)
        If Cell.Value = "Monitor" Then
             ' Copy>>Paste in 1-line (no need to use Select)
            .rows(Cell.Row).Copy Destination:=Sheets(4).rows(Cell.Row)
        End If
    Next Cell
    For Each Cell In .Range("D1:D" & .Cells(.rows.Count, "D").End(xlUp).Row)
        If Cell.Value = "Serialized Docks" Then
             ' Copy>>Paste in 1-line (no need to use Select)
            .rows(Cell.Row).Copy Destination:=Sheets(3).rows(Cell.Row)
        End If
    Next Cell
    For Each Cell In .Range("D1:D" & .Cells(.rows.Count, "D").End(xlUp).Row)
        If Cell.Value = "Peripheral" Then
             ' Copy>>Paste in 1-line (no need to use Select)
            .rows(Cell.Row).Copy Destination:=Sheets(5).rows(Cell.Row)
        End If
    Next Cell
    For Each Cell In .Range("D1:D" & .Cells(.rows.Count, "D").End(xlUp).Row)
        If Cell.Value = "Serialized Audio" Then
             ' Copy>>Paste in 1-line (no need to use Select)
            .rows(Cell.Row).Copy Destination:=Sheets(6).rows(Cell.Row)
        End If
    Next Cell
    For Each Cell In .Range("D1:D" & .Cells(.rows.Count, "D").End(xlUp).Row)
        If Cell.Value = "Modem" Or Cell.Value = "AP" Or Cell.Value = "Switch" Then
             ' Copy>>Paste in 1-line (no need to use Select)
            .rows(Cell.Row).Copy Destination:=Sheets(7).rows(Cell.Row)
           End If
    Next Cell
End With

End Sub

This part works as expected, except that it preserves the line position of the row it's copying - ergo, if a category defined in column D starts at line 47, the corresponding sheet will also start at 47, leaving a huge blank chunk. I tried to fix that for a while, but I'm not very good at scripting, so I resolved to use another script to delete the empty rows once the operation completes:

Sub Cleanup()

  Dim r As Range, rows As Long, i As Long, cnt As Long ' Define some variables
  Set r = Sheets(3).Range("A1:O2000") ' Set our range of effect
  rows = r.rows.Count ' Count rows in the range defined by R
  For i = rows To 1 Step (-1) ' Attach each defined row to variable I
    If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete ' Check if row is empty and if so, delete it
  Next ' Repeat ad nauseum
  
  Set r = Sheets(3).Range("A1:O2000")
  rows = r.rows.Count
  For i = rows To 1 Step (-1)
    If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete
  Next
  
  Set r = Sheets(4).Range("A1:O2000")
  rows = r.rows.Count
  For i = rows To 1 Step (-1)
    If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete
  Next
 
  Set r = Sheets(5).Range("A1:O2000")
  rows = r.rows.Count
  For i = rows To 1 Step (-1)
    If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete
  Next

  Set r = Sheets(6).Range("A1:O2000")
  rows = r.rows.Count
  For i = rows To 1 Step (-1)
    If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete
  Next

  Set r = Sheets(7).Range("A1:O2000")
  rows = r.rows.Count
  For i = rows To 1 Step (-1)
    If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete
  Next
End Sub

This also works as expected. My question is how can I set the range dynamically, such that if my source export is > 2000 lines, the script will adjust the "cleanup range" (defined by R in each section of the code above) to include it? There must be a way to get the maximum number of rows on my Dump page and use that as a variable to set the range for the following operations, but I can't figure it out.

Tried setting a variable with:

dim a As Long
Set a = Sheets(8).Range("D1:D" & .Cells(.rows.Count, "D").End(xlUp).Row)

but it came back expecting an object. Tried the same as a Str, replacing Set with Let or just a =, but no luck. I was hoping that would capture the highest filled cell in column D on Sheet 8 (the dump sheet), and I could then use that variable in the range variable for the cleanup operation:

Set r = Sheets(3).Range("A1:O" & a)

This resulted in "Invalid or unqualified reference."

Any suggestions?

I get invalid or unqualified reference when I try this one. – Dreadp1r4te Jun 30 '23 at 19:51 — Dreadp1r4te, Jun 30 '23 at 19:51

score 0 · Answer 1 · edited Jun 30 '23 at 20:19

I figured it out! I was defining my variable A as a range, but it wasn't a range as it was just returning a number; specifically the number of the highest filled row: 715. I changed it to a Long and presto:

Dim r As Range, rows As Long, i As Long, a As Long ' Define some variables
    With Sheets(8)
        a = .Cells(.rows.Count, "D").End(xlUp).Row ' Get max row from Dump sheet and store in variable A
    End With
  Set r = Sheets(2).Range("A1:O" & a)  ' Set our range of effect using predefined variable, define as R
  rows = r.rows.Count ' Count rows in the range defined by R
  For i = rows To 1 Step (-1) ' Attach each defined row to variable I
    If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete ' Check if row is empty and if so, delete it
  Next ' Repeat ad nauseum

Trying to set range dynamically in VBA / Excel based on dump sheet length

1 Answers1