| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing blank rows taking a long time
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
removing blank rows taking a long time
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
removing blank rows taking a long time
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" 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
removing blank rows taking a long time
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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
removing blank rows taking a long time
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" wrote: 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" 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pasting in Excel has started taking a long time. | Excel Discussion (Misc queries) | |||
| Processing taking a really long time | Excel Discussion (Misc queries) | |||
| Subtotalling taking long time to complete | Excel Worksheet Functions | |||
| How to delete blank rows at one time in a long excel spreadsheet? | Excel Worksheet Functions | |||
| Excel Taking Long TIme to Start | Excel Discussion (Misc queries) | |||
Linear Mode
