On May 10, 12:46 pm, tabbicat
wrote:
I have a list of various lengths (from 500 to 12000 rows) on which I want to
run a series of macros automatically then filter. The list has a column
containing dates in text format and blank rows between the months. If I want
to use Autofilter I have to remove the blank rows, so I wrote a macro to do
that. It does the job, but it takes about 23 seconds to do it. My other
macros run much faster, and I can't figure out why this one is so slow.
I would really appreciate any ideas on speeding it up because my next step
is to figure out how to run it automatically on hundreds of these lists.
Thanks in advance for any replies.
Here's the macro:

Sub RemoveBlanks()

Range("A401") = "Feb 1 *"
Range("B401").FormulaR1C1 = "=MATCH(RC[-1],C[12],0)-1"
Range("C401").FormulaR1C1 = "=ADDRESS(RC[-1],7,4)"
Range("D401").FormulaR1C1 = "=ADDRESS(RC[-2],19,4)"
Range("INDIRECT(C401):INDIRECT(D401)").Delete Shift:=xlUp
Range("A402") = "Mar 1 *"
Range("B402").FormulaR1C1 = "=MATCH(RC[-1],C[12],0)-1"
Range("C402").FormulaR1C1 = "=ADDRESS(RC[-1],7,4)"
Range("D402").FormulaR1C1 = "=ADDRESS(RC[-2],19,4)"
Range("INDIRECT(C402):INDIRECT(D402)").Delete Shift:=xlUp
Range("A403") = "Apr 1 *"
Range("B403").FormulaR1C1 = "=MATCH(RC[-1],C[12],0)-1"
Range("C403").FormulaR1C1 = "=ADDRESS(RC[-1],7,4)"
Range("D403").FormulaR1C1 = "=ADDRESS(RC[-2],19,4)"
Range("INDIRECT(C403):INDIRECT(D403)").Delete Shift:=xlUp
Range("A404") = "May 1 *"
Range("B404").FormulaR1C1 = "=MATCH(RC[-1],C[12],0)-1"
Range("C404").FormulaR1C1 = "=ADDRESS(RC[-1],7,4)"
Range("D404").FormulaR1C1 = "=ADDRESS(RC[-2],19,4)"
Range("INDIRECT(C404):INDIRECT(D404)").Delete Shift:=xlUp
Range("A405") = "Jun 1 *"
Range("B405").FormulaR1C1 = "=MATCH(RC[-1],C[12],0)-1"
Range("C405").FormulaR1C1 = "=ADDRESS(RC[-1],7,4)"
Range("D405").FormulaR1C1 = "=ADDRESS(RC[-2],19,4)"
Range("INDIRECT(C405):INDIRECT(D405)").Delete Shift:=xlUp
Range("A406") = "Jul 1 *"
Range("B406").FormulaR1C1 = "=MATCH(RC[-1],C[12],0)-1"
Range("C406").FormulaR1C1 = "=ADDRESS(RC[-1],7,4)"
Range("D406").FormulaR1C1 = "=ADDRESS(RC[-2],19,4)"
Range("INDIRECT(C406):INDIRECT(D406)").Delete Shift:=xlUp
Range("A407") = "Aug 1 *"
Range("B407").FormulaR1C1 = "=MATCH(RC[-1],C[12],0)-1"
Range("C407").FormulaR1C1 = "=ADDRESS(RC[-1],7,4)"
Range("D407").FormulaR1C1 = "=ADDRESS(RC[-2],19,4)"
Range("INDIRECT(C407):INDIRECT(D407)").Delete Shift:=xlUp
Range("A408") = "Sep 1 *"
Range("B408").FormulaR1C1 = "=MATCH(RC[-1],C[12],0)-1"
Range("C408").FormulaR1C1 = "=ADDRESS(RC[-1],7,4)"
Range("D408").FormulaR1C1 = "=ADDRESS(RC[-2],19,4)"
Range("INDIRECT(C408):INDIRECT(D408)").Delete Shift:=xlUp
Range("A409") = "Oct 1 *"
Range("B409").FormulaR1C1 = "=MATCH(RC[-1],C[12],0)-1"
Range("C409").FormulaR1C1 = "=ADDRESS(RC[-1],7,4)"
Range("D409").FormulaR1C1 = "=ADDRESS(RC[-2],19,4)"
Range("INDIRECT(C409):INDIRECT(D409)").Delete Shift:=xlUp
Range("A410") = "Nov 1 *"
Range("B410").FormulaR1C1 = "=MATCH(RC[-1],C[12],0)-1"
Range("C410").FormulaR1C1 = "=ADDRESS(RC[-1],7,4)"
Range("D410").FormulaR1C1 = "=ADDRESS(RC[-2],19,4)"
Range("INDIRECT(C410):INDIRECT(D410)").Delete Shift:=xlUp
Range("A411") = "Dec 1 *"
Range("B411").FormulaR1C1 = "=MATCH(RC[-1],C[12],0)-1"
Range("C411").FormulaR1C1 = "=ADDRESS(RC[-1],7,4)"
Range("D411").FormulaR1C1 = "=ADDRESS(RC[-2],19,4)"
Range("INDIRECT(C411):INDIRECT(D411)").Delete Shift:=xlUp

End Sub

The cells in columns A:F are used for calculations to row 369.
The columns are as follows:
A B C D E F G H I J K L
M N O
Pt1 Hs1 Aspect Pt2 Hs2 Event
Type Day Time

Thanks again.

Hello Tabbicat,

You can use the SpecialCells method to locate only Blank cells in a
range. This macro will remove all blanks from the ActiveSheet.


Sub RemoveBlankRows()

Dim Blank As Range
Dim BlankRows As Range

With Activesheet
Set BlankRows = .UsedRange.SpecialCells(xlCellTypeBlanks)
If BlankRows.Count = 0 Then Exit Sub
For Each Blank In BlankRows.Areas
Blank.Delete Shift:=xlShiftUp
Next Blank
End With

End Sub

Sincerely,
Leith Ross