Attempting to create/align tab headers based on chart. (No macros in file attachment.) - test.xls (0

[Tom Walat]

Using Excel 2000:
I realize this is not proper newsgroup etiquette and I
apologize in advance for attaching an Excel file but trying to explain
what I need to do will be even more difficult. (There are no macros in
this file.)
Is there a way, by using Row 4 as a header template, to adjust
all the other rows with text (with their corresponding dollar amounts
below them) to line up properly?
Ideally, I'd like the "money" headers to shift over to start
in Column B and the names merged into a single cell in Column A, but I
think I'm already pushing it.
Thanks for any help.
- Tom

 

[Frank Kabel]

Hi Tom
no need for an attachment. Some sample rows of your data in plain text
would have been sufficient.
Try the following:
- insert a blank column A in your sheet
- start the following macro (please do this on a backup copy of your
data)

Sub change_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
With Cells(RowNdx, "B")
If Trim(.Value) = "REGULAR CITY" Then
.Offset(0, -1).Value = .Offset(-1, 0).Value & " " &
..Offset(-2, 0).Value
.Offset(2, 0).Resize(3, 1).EntireRow.Delete
End If
End With
Next RowNdx
Range("1:3").EntireRow.Delete
End Sub

 

[Don Guillett]

Then you will understand why some won't answer. Best to state your problem
and be INVITED to send a file off list. Not nice to tie up all just for you.
It's the size of the file.

 

[Tom Walat]

Frank:
I appreciate the response.
I was hoping the macro would align the columns by shifting the
cells that didn't match the "template row."
I basically wanted everything to line up under the Row 4
columns.
After running your macro, I can see under Column E that it has
OUT OF GRADE OVERTIME in E1 but all the other entries under that are
VACATION. In the next column in F1 is VACATION but below that are SICK
CITY.
I probably didn't explain myself well enough. Thanks for
taking a swing at it.
- Tom