Here is the actual problem I'm trying to solve not the best I know but
also a learning tool in finding out what is in cells etc, with code.
.I'm actually trying to delete rows I don't want. Here is a sample of
report that is imported. It contains 3 columns of which column A & B are
the ones with data Want it to only keep name and cell with 23 blanks
before the Reg: which are the hours of pay. Name always comes after
blank Row. The header page info needs to be deleted also. I think a 3
condition Do While loop will do it. I need to put in something near the
bottom of the report in one of the blank rows to stop the Do While loop.
First two names in example is what report should end up like. What is
crazy the code I'm also posting is giving me errors in debug on lines
that work in another spreadsheet to hide rows. More comments after this
example.
Column A
Column B
WILLIAMS
ANDREA LASHUN 0001004784 200400
Reg: 40.00 OT: 1.00
ZEIGLER
EVELYNIA 1000797 200400
Reg: 37.50
Some Hotel
Projected Hours Report
Page 0016
05/26/08 -> 06/01/08 06/09/2008
4:43p
All accounts
all pay rules
For acct 200500 : ROOM SERVICE
CLOUD
JOE H 0001004829 200500
ROOM SERVICE PT All
E 790764 3 Room Service Waitperson
Off
08/22/2007
Days worked : 7
Acct:200500
Reg: 40.00 OT: 19.25 Tips:
$220.0000
COLEMAN
LARRY DARNELL 0001004796 200500
ROOM SERVICE FT All
DE 790394 3 Room Service Waitperson
Off
12/21/200706/25/200806/25/2007
Days worked : 1
Acct:200500
Reg: 9.00 Tips: $34.6800
KILGORE
SHARON DELOIS 0001004455 200500
ROOM SERVICE FT All
E 790331 3 Room Service Waitperson
Off
03/19/200609/21/200609/09/2005
Days worked : 7
Acct:200500
Reg: 40.00 OT: 11.00
VANCOURT
PATRICK ALLAN 0001004891 200500
ROOM SERVICE PT 30
Min Lunc
E 790824 3 Room Service Waitperson
Off
02/18/2008
Days worked : 5
Acct:200500
Reg: 40.00 OT: 3.25 Other:
$96.1700
Some Hotel
Projected Hours Report
Page 0017
05/26/08 -> 06/01/08 06/09/2008
4:43p
CODE I WAS TRYING TO MODIFY, first macro is one I was trying to make
work. LoopRange 3() after is the one that works in another spreadsheet
but bombs in this one. May be something to do with what cell I'm in.
Cellpointer is in column A and it stops on
Set currCell = ActiveSheet.Cells(x, 0) (says Application-defined or
Object defined error).
Sub Deleterows()
'Start at the currently selected cell
Dim x As Integer
Dim curr
Dim dontdo$
dontdo = -123
x = ActiveCell.Row
ActiveWorkbook.Sheets("wk1").Select
Do While ActiveCell(x, 0).Value <> dontdo
curr = ActiveCell.Offset(0, 0)
'Test for blank
If Cells(x, 0).Value = "" Then
Selection.Cells(1, 1).Select
Selection.EntireRow.Delete
x = x + 1
Selection.Offset(1, 0).Select
ElseIf Cells(x, 0).Value = "Some Hotel" Then
Selection.Cells(1, 1).Select
Selection.EntireRow.Delete
x = x + 1
Selection.Offset(1, 0).Select
Else
If Cells.Text.Left(27) = " Reg:"
Then
x = x + 1
Selection.Offset(1, 0).Select
Else
Selection.Cells(1, 1).Select
Selection.EntireRow.Delete
End If
End If
curr = ActiveSheet.Cells(x, 0)
Loop
End Sub
Sub LoopRange3()
'Start at the currently selected cell
Dim x As Integer, currCell As Range
x = ActiveCell.Row
Set currCell = ActiveSheet.Cells(x, 0)
Do While Cells(x, 0).Value <> ""
'Test for zero
If Cells(x, 0).Value = 0 And Cells(x, 9).Value = 0 Then
Selection.Cells(1, 1).Select
Selection.EntireRow.Hidden = True
x = x + 1
Selection.Offset(1, 0).Select
Else
x = x + 1
Selection.Offset(1, 0).Select
End If
Set currCell = ActiveSheet.Cells(x, 6)
Loop
End Sub