Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

newbie needs help

  Asked By: Adelina    Date: Nov 15    Category: MS Office    Views: 764
  

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

Share: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on newbie needs help Or get search suggestion and latest updates.




Tagged: