Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Sussex, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro or Formula? (Excel 2000 SR1)

    I have a worksheet which lists items at the start of each row in column A and then the cells in column G corresponding to the row label may or may not have a numerical value in them. I want to start at Cell G1 and if its value is >0 I want to copy the row label and the value in the corresponding cell in column G onto a new worksheet alonside each other and then do the same procedure with the next cell in column G. This effectively removes all rows with no value or zero value so I can then construct a chart from the resulting worksheet. How do I structure the formula or macro to do this as I am struggling being new to macros at the moment. Any help would be gratefully received.

    Thanks

    Simon

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro or Formula? (Excel 2000 SR1)

    I would sort the first worksheet on Column G, descending, then copy all rows where the value in Col G is > 0, paste this into a new sheet, then delete Cols B-F.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Macro or Formula? (Excel 2000 SR1)

    If you sort the data before pasting what you want onto another worksheet, the order may change, which might not be desirable.

    Here's a macro that takes the data from columns A & G on Sheet1, pastes it into columns A & B on Sheet2, then deletes all rows on Sheet 2 that have non-numeric data, or zeros, in Column B:

    Sub CleanUp()
    '
    Dim A As Long
    Dim B As Long
    Dim C As Range

    On Error GoTo Abort
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Sheets("Sheet1").Select
    Range("A:A,G:G").Select
    Selection.Copy
    Range("A1").Select
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Range("A1").Select

    Set C = ActiveSheet.UsedRange.Rows
    B = 0
    For A = C.Rows.Count To 1 Step -1
    If Application.WorksheetFunction.Sum(C.Rows(A).Column s("B:B")) = 0 Then
    C.Rows(A).EntireRow.Delete
    B = B + 1
    End If
    Next A

    Abort:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    New Lounger
    Join Date
    Apr 2002
    Location
    Sussex, England
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro or Formula? (Excel 2000 SR1)

    Thanks for your help - this was just what I wanted. It also is an education to see how it can be done, hopefully I will learn from it.

    Simon

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro or Formula? (Excel 2000 SR1)

    Autofilter (Tools-Filter-Autofilter) would perform the same function without resorting to macros or formulas.

    Just set the criteria to greater than 0 in column G.

    HIH

Posting Permissions

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