Dynamic Range

adept

New Member
Joined
Feb 13, 2009
Messages
46
Greetings All,

I have a macro (I did not write) that I have used for some time, that really is getting bogged down because of all the rows (some 40,000) it has to cycle through. I would like to have it dynamically figure just the range that it needs to cycle to save time.

The following are counts of rows that I would like the range to be built from. These change each day so the range will change and only work on the rows between the two counts.
PHP:
    ArngCount = Sheets("Items").Range("A1", Sheets("Items").Range("A65536").End(xlUp)).Rows.Count
    ErngCount = Sheets("Items").Range("E1", Sheets("Items").Range("E1").End(xlDown)).Rows.Count
The error that I get with the following is "#13 Type Mismatch"

PHP:
Inter = "Intersect(Range(""E" & ErngCount & ":E" & ArngCount & """), ActiveSheet.UsedRange)"
Set rng = Inter
PHP:
Sub Delete_Blank_Rows222222()
' This macro deletes all rows on the active worksheet
' that have "" column E.
 Dim rng As Range, cell As Range, del As Range
 
 Sheets("Items").Select
 ActiveSheet.Unprotect
 SpeedOn 'Fires Code
' The line below is what I would like to have dynamic E#:E# (E ErngCount:E ArngCount
Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange)
 For Each cell In rng
   If (cell.Value) = "" Then
       If del Is Nothing Then
          Set del = cell
       Else: Set del = Union(del, cell)
       End If
    End If
 Next cell
 On Error Resume Next
 del.EntireRow.Delete
 SpeedOff 'Fires Code
 ActiveSheet.Protect
End Sub

Hope someone can help.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
If you post with Code tags (rather than PHP tags), people can copy paste from the board directly to a module without introducing non-standard characters.

The posted sub will delete all rows where the column E entry is "".
Are your ErngCount and ArngCount soley for refining that or to restrict the action, possibly leaving blanks in E?

I am concerned that, as written, you might end up with a larger loop than the original code. (Consider if both A and E are blank).

Does your worksheet have desired blank rows at the top?
 
Upvote 0
Mike,

Thanks for the info! I'll use Code tags from now on.

I would like for example the range to be E39997:E40005 which are counted by the rngCount.

I only have blank rows at the bottom (not consecutive) which I want to be deleted.
 
Last edited:
Upvote 0
DeleteBlankE2 should do exactly as you want, but (depending on if there are any formulas in colE that return "") DeleteBlankE3 might also serve.
Code:
Sub DeleteBlankE2()
    Dim del As Range, oneCell As Range
    With ThisWorkbook.Sheets(1)
        Set del = .Cells(.Rows.Count, 1)
        For Each oneCell In Range(del.End(xlUp), .Cells(1, 5).End(xlDown)).Columns(5).Cells
            If oneCell.Value = vbNullString Then
                Set del = Application.Union(del, oneCell)
            End If
        Next oneCell
    End With
    del.EntireRow.Delete
End Sub

Sub DeleteBlankE3()
    With ThisWorkbook.Sheets(1)
        With Range(.Cells(.Rows.Count, 1).End(xlUp), .Cells(1, 5).End(xlDown)).Columns(5).Cells
            On Error Resume Next
            .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            On Error GoTo 0
       End With
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top