macro to move line (Excel 2003)

Topic

New Reply

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.

Reply | Quote

Replies

You didn’t attach a workbook.

Reply | Quote

oops

Reply | Quote

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

Reply | Quote

yes, it worked perfectly. Thank you very much.

Reply | Quote