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.
Code:
    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"

Code:
Inter = "Intersect(Range(""E" & ErngCount & ":E" & ArngCount & """), ActiveSheet.UsedRange)"
Set rng = Inter
Code:
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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,917
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?
 

adept

New Member
Joined
Feb 13, 2009
Messages
46
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:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,917
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

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
Top