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.
The error that I get with the following is "#13 Type Mismatch"
Hope someone can help.
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
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.