removing blank rows taking a long time

T

tabbicat

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.
 
L

Leith Ross

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.
Click to expand...

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
 
C

Cory

tabbicat - I've got a simple procedure that I use to delete blank rows that
seems to work pretty well. It uses the Special Cells property to select all
of the blank rows in a selection, which can then be deleted as per usual.
Just select the column containing the blank rows that you want deleted and
then run the following:

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Hope this helps.

-Cory

tabbicat said:
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.
Click to expand...
 
T

tabbicat

Thank you for the reply. I have tried your macro on the range where my list
is located, but it takes 6.5 minutes to run. Then I tried adjusting it and
got it down to 18 seconds. That requires the loop portion to run twice
though. I just reread your post a third time, and now I am going to try the
original macro on the list after moving it to a sheet by itself.
Thanks again.

Leith Ross said:
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.
Click to expand...

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
Click to expand...
 
T

tabbicat

Thank you for the reply. The specialcells function is helpful to know about.
My list has entries in columns next to it though, so I will have to move it
to its own sheet. I am still trying it though.
Thanks again.

Cory said:
tabbicat - I've got a simple procedure that I use to delete blank rows that
seems to work pretty well. It uses the Special Cells property to select all
of the blank rows in a selection, which can then be deleted as per usual.
Just select the column containing the blank rows that you want deleted and
then run the following:

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Hope this helps.

-Cory

tabbicat said:
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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why are the two codes below not getting the right formula for myworksheet event change formula? 2
display calculation next row 5
Insert row gives run-time error 13 message 1
Copying Data Down the Column But Only Based on What's in Column A 3
how do i separate addresses and place results in another cell 4
Trouble selecting multiple sheets using RDB code 13
Sorting Problem 2
The code below worked for a few days and just totally stoppedworking. 1

Top