Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    macro to move line (Excel 2003)

    I need to go through a worksheet and delete the numbers (i started highlighting them in red) and move up the percentages in their place. I've manually done it on the Formatted worksheet attached. How would I make a macro to go through the whole worksheet and move the percentages up. I need to keep the whole number next to the words UNWEIGHTED TOTAL, those rows and the TOTAL AND TOTAL RESPONDING should not be changed. Thank you for the help.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to move line (Excel 2003)

    You didn't attach a workbook.
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro to move line (Excel 2003)

    oops

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro to move line (Excel 2003)

    Does this do what you want?

    Sub TestMacro()
    Dim lngLastRow As Long
    Dim lngLastCol As Long
    Dim lngRow As Long
    Dim wsh As Worksheet
    Set wsh = ActiveSheet
    With wsh
    ' Find the last row
    lngLastRow = .Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    ' Find the last column
    lngLastCol = .Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns).Column
    ' Loop backwards
    For lngRow = lngLastRow To 2 Step -1
    ' Test for % format
    If InStr(.Range("B" & lngRow).NumberFormat, "%") > 0 Then
    ' Test for special text in column A
    Select Case .Range("A" & (lngRow - 1))
    Case "TOTAL", "TOTAL RESPONDING", _
    "UNWEIGHTED TOTAL"
    ' Leave alone
    Case Else
    ' Move cells up
    .Range(.Cells(lngRow - 1, 2), .Cells(lngRow - 1, lngLastCol)). _
    Delete Shift:=xlShiftUp
    .Range("A" & lngRow).Delete Shift:=xlShiftUp
    lngRow = lngRow - 1
    End Select
    End If
    Next lngRow
    End With
    End Sub

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro to move line (Excel 2003)

    yes, it worked perfectly. Thank you very much.

Posting Permissions

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