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
Macro to Delete Rows
PCSQL66
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
Angry Coder
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
RobertMC70
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
Eder Andr&#233&#59;s
andy at andypope dot info
jqq
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
GeoffLloyd