Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Clemmons, North Carolina, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel - With Statement

    I am trying to find all "-" on a Worksheet. When I find one, I want to copy the EntireRow to a different worksheet, delete the blank row on the original Worksheet and then start finding the "-"'s again. I want to loop through then entire Range until I have cut out all the rows that have a "-" Here is the code I have. I am currently getting an error message. It's like it can't refind the range after it goes and copies my row onto the different Worksheet.
    ...
    With Worksheets(cursheet).Range("j1:j65536")
    Range("j1:j65536").Select
    Set c = .Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False)
    If Not c Is Nothing Then
    firstaddress = c.Address
    Do
    c.Activate
    Call move_neg
    Worksheets(cursheet).Activate
    Range("j1:j65536").Select
    c.Activate
    Set c = .FindNextŠ
    Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
    End With
    ...
    Sub move_neg()
    ActiveCell.EntireRow.Cut
    Sheets("Negative Hours").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Activate
    End Sub


    Thanks!

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - With Statement

    Hi,

    If this were a one time action, I would:

    - Sort the range in question on the J-column (brings together all rows that have a "-" in that column)
    - cut all these rows in one go
    - paste them in the destination sheet.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Jul 2002
    Location
    Clemmons, North Carolina, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - With Statement

    Yes, I thought about that, but the Worksheet has 38,000+ rows and I was afraid sorting would crash my computer. Don't you think? I guess it's worth a try.
    Thanks!

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel - With Statement

    Try an auto filter for all values less than zero and cut and paste.

    Andrew

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - With Statement

    Hi Ken,

    Try the following code:

    Sub MoveNegative()
    Dim a As Integer, b As Integer
    a = ThisWorkbook.Sheets(1).UsedRange.Rows.Count
    With ThisWorkbook.Sheets(1)
    For b = 1 To a
    If .Cells(b, 1).Value = "-" Then
    Cells(b, 1).EntireRow.Select
    Selection.Copy
    ThisWorkbook.Sheets("Negative Hours").Activate
    ActiveSheet.Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    ThisWorkbook.Sheets(1).Select
    Cells(b, 1).EntireRow.Delete
    End If
    Next
    End With
    Application.CutCopyMode = False
    End Sub

    I could not tell what the name of the sheet you were moving from is, so I just referred to it by "Sheet(1)". You will have to put in whatever the sheet name is. From your post it looked as though you were testing values in Column J - the code above tests in Column 1 so you will have to change this to test in Column J.

    Just a thought though...it might be more logical to test whatever value or function that causes the hours to be negative than to test for the "-"...

    You might want to test this on a blank sheet to make sure it gives you the results that you want, since it deletes the rows with "-".

    Thanks,

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - With Statement

    You may want to change the line

    for b = 1 to a

    to

    for b = a to 1 step -1

    otherwise I think you're going to find that when you are on line 10 and delete it, line 11 will become line 10 and not get checked, plus, if you remove a lot of lines, the code will loop needlessly through empty space for a while.

    Brooke

  7. #7
    New Lounger
    Join Date
    Jul 2002
    Location
    Clemmons, North Carolina, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - It worked.

    That worked! Thanks a bunch.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - With Statement

    Hi,

    <<I was afraid sorting would crash my computer. Don't you think? I guess it's worth a try.>>

    If you're afraid of a crash, just make sure you backup the document before trying.
    I guess the manual method is less risky than your macro trick, because it takes less steps.

    Another tip: try my Autosafe utility, it is a significant enhancement to the default XL autosave add-in. Find it at:

    http://www.bmsltd.ie/mvp

    Edited Mar 13th 2004 to update link
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •