GB Allan


Hi, I am writing a macro that I want to do the following with:

1-Sort spreadsheet by column J.

2-Search for the first instance of the term "unbilled."

3-From that point and down, delete all rows below.

4-Delete specfic columns.

I'm having trouble with steps 2 and 3 - the search works, but for the deleting of the rows, it's picking a specific row range. I want it to delete all rows from the first instance of the search term, down to the bottom of the spreadsheet. Each spreadsheet will have a different row range for this, so it can't be a specific range.

Any help I can receive would be greatly appreciated. Here is the code:

Sub ()
'
' Macro
' Macro recorded 1/4/2007 by ___________'
' Keyboard Shortcut: Ctrl+a
'
Cells.Select
Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.Find(What:="unbilled", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("A2807").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Rows("2807:3529").Select
Selection.Delete Shift:=xlUp
Range("A:A,C:C,D:D,G:G,H:H").Select
Range("H1").Activate
ActiveWindow.SmallScroll ToRight:=5
Range("A:A,C:C,D:D,G:G,H:H,Q:Q,R:R").Select
Range("R1").Activate
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub

Thank you,

Gary




Re: Macro to Delete Rows

Andy Pope


Hi,

Does this work for you
As you are deleting information please test on a copy of your workbook.

Sub Gary()
'
' Macro
' Macro recorded 1/4/2007 by ___________'
' Keyboard Shortcut: Ctrl+a
'
Dim rngFind As Range

Cells.Select
Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Set rngFind = Selection.Find(What:="unbilled", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not rngFind Is Nothing Then
Range(rngFind, ActiveCell.SpecialCells(xlLastCell)).Delete Shift:=xlUp
End If
Range("A:A,C:C,D:D,G:G,H:H,Q:Q,R:R").Delete Shift:=xlToLeft

End Sub







Re: Macro to Delete Rows

GB Allan

Hi Andy,

It doesn't appear to be working. Basically, by sorting by column J, it brings all the releveant info that I need to the top. Then by searching for the string "unbilled" it brings me to the very first row below that relevant information. Then everything from there down needs to be deleted.

I ran the macro and the information I need to delete is still in there.

There might be another way to tackle it - in column J, there are contract numbers. They are 8 digit numbers starting with the number 5 (e.g., 51158882, 58882442, 53333333, etc.). By sorting, they come to the top. So perhaps it would be easier to write something that knows when the contract numbers stop From that point on and below, everything needs to be deleted.

I also have an issue with the "select all" at the beginning. The data is not contiguous, so depending on where the cursor us when you start it, sometimes it selects everything and sometimes it doesn't.

I hope my explanation makes sense. Thank you for your help.

Gary






Re: Macro to Delete Rows

Andy Pope

Can you put together an example workbook and email it to me

andy at andypope dot info






Re: Macro to Delete Rows

ska67can

Try this after the sort:

Dim i As Integer

For i = 1 To Cells(10000, 10).End(xlUp).Row
If Cells(i, 10) = "unbilled" Then
Exit For
End If
Next

Range(Cells(i, 10), Cells(10000, 10).End(xlUp)).EntireRow.Delete

ska





Re: Macro to Delete Rows

Andy Pope

with the help of a test file from Gary this revised code appears to do the trick.

Sub Gary()
'
' Macro
' Macro recorded 1/4/2007 by ___________'
' Keyboard Shortcut: Ctrl+a
'
Dim rngFind As Range

Cells.Select
Selection.Sort Key1:=Range("J1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Set rngFind = Selection.Find(What:="unbilled", _
After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not rngFind Is Nothing Then
Range(rngFind, _
ActiveCell.SpecialCells(xlLastCell)).EntireRow.Delete Shift:=xlUp
End If
Range("A:A,C:C,D:D,G:G,H:H,Q:Q,R:R").Delete Shift:=xlToLeft

End Sub






Re: Macro to Delete Rows

GB Allan

Thank you so much Andy - am very appreciative. Works great!