Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more macro
I have a worksheet. Starting in row 21, I want it to see if the entry in
column A is a number. If it is, I want it to go down one row and try again, i.e., test cell A22 to see if it is a number. If cell A21 is not a number, I want it to delete that entire row and then look at the entry in the new cell A21. If I have to put a maximum on it, I'd say there will not be more than 600 rows to test. When it is done with this, I would like it to highlight the block from cell A21 to the last row in column E. I would then like it to sort that block of data by the column B entry, in ascending order. I would then like to copy all data from cell A1 (the top left corner) down to the bottom right corner of that same block and have it paste it into another file of my choice, starting at its cell A1 of one or two worksheets. It would be pasted into one of two worksheets, either BOPCompHldgs or EOPCompHldgs, so I would need it to ask me which of the two sheets I want to paste into. There's a few other things after that, but I think I can handle that myself from the other stuff I've learned. Kindly help. Grace |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more macro
Grace
Here is the code for the first part of your request Delete rows where cell in column a is not numeric & sort data Deletion code starts from the last row and works back to row 21 as thi is the easiest way to code row deletions I did not understand fully where you wanted to copy A1 down to so di not include that part in the code Sub dddd() Dim lRow As Long For lRow = Range("a" & Rows.Count).End(xlUp).Row To 21 Step -1 If Not IsNumeric(Cells(lRow, "a")) Then Rows(lRow).Delete End If Next lRow lRow = Range("a" & Rows.Count).End(xlUp).Row Range("a21:e" & lRow).Sort _ Key1:=Range("B21"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Su -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more macro
This seems to eliminate only rows that are totally empty. Try it. I want
it to eliminate all rows where the entry in column A is not a number. If there is a number in column C, but not in column A, I would want to delete that row. That said, it seems to be leaving rows where column C has an entry that is not a number, just some text. Thanks Dean "mudraker " wrote in message ... Grace Here is the code for the first part of your request Delete rows where cell in column a is not numeric & sort data Deletion code starts from the last row and works back to row 21 as this is the easiest way to code row deletions I did not understand fully where you wanted to copy A1 down to so did not include that part in the code Sub dddd() Dim lRow As Long For lRow = Range("a" & Rows.Count).End(xlUp).Row To 21 Step -1 If Not IsNumeric(Cells(lRow, "a")) Then Rows(lRow).Delete End If Next lRow lRow = Range("a" & Rows.Count).End(xlUp).Row Range("a21:e" & lRow).Sort _ Key1:=Range("B21"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more macro
Grace
The macro deleted any row that had a text entry in column A I have modified it to delete all rows where the cell in column a i text or blank Sub dddd() Dim lRow As Long For lRow = Range("a" & Rows.Count) _ .End(xlUp).Row To 21 Step -1 If Not IsNumeric(Cells(lRow, "a")) _ Or Cells(lRow, "a") = "" Then Rows(lRow).Delete End If Next lRow lRow = Range("a" & Rows.Count).End(xlUp).Row Range("a21:e" & lRow).Sort _ Key1:=Range("B21"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Su -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more macro
I think there is a typo between rows three and four, but I think I got it
working. The only strange thing is that it does not seem to be deleting the bottom-most row which does NOT have anything in column A. Actually, since it is always an interesting grand total row, I like it better this way! But it is curious. Does the macro automatically assume the bottom-most row is a "keeper"? Thanks! G "mudraker " wrote in message ... Grace The macro deleted any row that had a text entry in column A I have modified it to delete all rows where the cell in column a is text or blank Sub dddd() Dim lRow As Long For lRow = Range("a" & Rows.Count) _ End(xlUp).Row To 21 Step -1 If Not IsNumeric(Cells(lRow, "a")) _ Or Cells(lRow, "a") = "" Then Rows(lRow).Delete End If Next lRow lRow = Range("a" & Rows.Count).End(xlUp).Row Range("a21:e" & lRow).Sort _ Key1:=Range("B21"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more macro
Grace
It looks like a . disappeeared out of my code some how My code looks upwards in column A from the very last row until i finds an entry in column A As you last row of totals does not have an entry in column A my cod does not see that row This version finds the last used row on the sheet regardsless of whic column that entry Sub Take3() Dim lRow As Long For lRow = Cells.Find(what:="*", _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row To 21 Step -1 If Not IsNumeric(Cells(lRow, "a")) _ Or Cells(lRow, "a") = "" Then Rows(lRow).Delete End If Next lRow lRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row Range("a21:e" & lRow).Sort _ Key1:=Range("B21"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Su -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more macro
Thanks so much.
"mudraker " wrote in message ... Grace It looks like a . disappeeared out of my code some how My code looks upwards in column A from the very last row until it finds an entry in column A As you last row of totals does not have an entry in column A my code does not see that row This version finds the last used row on the sheet regardsless of which column that entry Sub Take3() Dim lRow As Long For lRow = Cells.Find(what:="*", _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row To 21 Step -1 If Not IsNumeric(Cells(lRow, "a")) _ Or Cells(lRow, "a") = "" Then Rows(lRow).Delete End If Next lRow lRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row Range("a21:e" & lRow).Sort _ Key1:=Range("B21"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more macro
Actually, there is one other little problem. Usually, these files can have
10,000 rows. It seems like this takes maybe 10 minutes. Is there a way to make this measurably faster, other than upgrading my year-old computer? Something like turning calc off or screenupdating off? If so, kindly tell me the commands. D "mudraker " wrote in message ... Grace It looks like a . disappeeared out of my code some how My code looks upwards in column A from the very last row until it finds an entry in column A As you last row of totals does not have an entry in column A my code does not see that row This version finds the last used row on the sheet regardsless of which column that entry Sub Take3() Dim lRow As Long For lRow = Cells.Find(what:="*", _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row To 21 Step -1 If Not IsNumeric(Cells(lRow, "a")) _ Or Cells(lRow, "a") = "" Then Rows(lRow).Delete End If Next lRow lRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row Range("a21:e" & lRow).Sort _ Key1:=Range("B21"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more macro
Actually. though I would still appreciate any time-saving suggestions per my
prior, as yet unanswered, post, I have since noticed a pattern, about 50 rows of data (that I want to keep), followed by about 13 almost empty rows (that I want to delete), that I believe won't ever change and which I can exploit to save oodles of time. 1) I want the macro to go to cell A14. 2) Then I want to go end-down. 3) Wherever the cursor lands after end-downing, I want to delete that row and the next 12 rows. However, if this brings you to row 65536, I simply want to return the cursor to cell A1 and end the macro. 4) Repeat (at the very most, in my wildest imagination, this could be repeated up to 1,400 times) steps 2 and 3 until the macro is ended (as defined in step 3) I note that it seems that (at least when done manually), at the end of step 3, the cursor will be in the first row of the next datablock. I assume the cursor is, in essence, in column A, though, perhaps, the column position is not really defined. But, it appears that, if I repeat step 2, the cursor returns to column A, which is where I want it to be. That's it! Can you help me, please? Grace "Grace" wrote in message ... Actually, there is one other little problem. Usually, these files can have 10,000 rows. It seems like this takes maybe 10 minutes. Is there a way to make this measurably faster, other than upgrading my year-old computer? Something like turning calc off or screenupdating off? If so, kindly tell me the commands. D "mudraker " wrote in message ... Grace It looks like a . disappeeared out of my code some how My code looks upwards in column A from the very last row until it finds an entry in column A As you last row of totals does not have an entry in column A my code does not see that row This version finds the last used row on the sheet regardsless of which column that entry Sub Take3() Dim lRow As Long For lRow = Cells.Find(what:="*", _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row To 21 Step -1 If Not IsNumeric(Cells(lRow, "a")) _ Or Cells(lRow, "a") = "" Then Rows(lRow).Delete End If Next lRow lRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row Range("a21:e" & lRow).Sort _ Key1:=Range("B21"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub --- Message posted from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more macro
Grace
Whislst I sit back and think up a way of increasing the speed add this single line of code (in red) at the start of the macro Dim lRow As Long Application.ScreenUpdating = False Add this line of code (in red) at the end before Application.ScreenUpdating = True End Sub One idea i have to speed up the process is to tag a helper column with all rows that need to be deleted then sort on the helper column so all rows that need deleting are together and can then be deleted at the same time --- Message posted from http://www.ExcelForum.com/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more macro
Grace
This version tags all non numeric entries in column A with zzzzDelete It then sorts the data. Finds the 1st row with zzzzzDelete in it an Deletes that row and all following rows. It does not change any data where the cell in column A is numeric Please try on a back up copy of your data Sub Take4() Dim lRow As Long Dim LastRow As Long Application.ScreenUpdating = False LastRow = Cells.Find(What:="*", _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row For lRow = LastRow To 21 Step -1 If Not IsNumeric(Cells(lRow, "a")) _ Or Cells(lRow, "a") = "" Then Cells(lRow, "a").Value = "zzzzDelete" End If Next lRow lRow = Columns("a").Find(What:="zzzzDelete", _ After:=Range("A20"), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True).Row Rows(lRow & ":" & LastRow).Delete Application.ScreenUpdating = True End Su -- Message posted from http://www.ExcelForum.com |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more macro
The turning off of screen updating seems to save about 5 seconds in a 4
minute routine! I can't seem to get the zz thing to work correctly, though it seems pretty clever. Did you read my last e-mail? I think that has the key to really knocking most of the time off. Any chance you (or anyone else) could write that macro? It is really short. Thanks Grace "mudraker " wrote in message ... Grace This version tags all non numeric entries in column A with zzzzDelete. It then sorts the data. Finds the 1st row with zzzzzDelete in it and Deletes that row and all following rows. It does not change any data where the cell in column A is numeric Please try on a back up copy of your data Sub Take4() Dim lRow As Long Dim LastRow As Long Application.ScreenUpdating = False LastRow = Cells.Find(What:="*", _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row For lRow = LastRow To 21 Step -1 If Not IsNumeric(Cells(lRow, "a")) _ Or Cells(lRow, "a") = "" Then Cells(lRow, "a").Value = "zzzzDelete" End If Next lRow lRow = Columns("a").Find(What:="zzzzDelete", _ After:=Range("A20"), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True).Row Rows(lRow & ":" & LastRow).Delete Application.ScreenUpdating = True End Sub --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
How to end macro on inital active worksheet containing macro button that was clicked | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |