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

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

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/

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

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/

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

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/

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

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/