Please help! ...again

[LucyN]

I posted last week with the same question, was suggested this code. It
actually deletes my entire data set.

I need a macro to
move the numbers up to Bangor and delete the space between the two rows.

BANGOR ME . . . . . . . . .
54 54 45 45
BATON ROUGE LA. . . . . . .
65 56 56 87



Sub Copydata()
Dim lr As Long, lc As Long, i As Long
lr = ActiveSheet.UsedRange.Rows.Count
lc = ActiveSheet.UsedRange.Columns.Count
For i = lr To 2 Step -2
Range(Cells(i, 2), Cells(i, lc)).Copy Cells(i - 1, 2)
Rows(i).Delete
Next i
End Sub

 

[Bernie Deitrick]

Lucy,

The code works perfectly for me, but maybe you have extra rows in your used range, so try to find
the last row of the data that you are interested in:

Sub Copydata()
Dim lr As Long, lc As Long, i As Long
lr = Cells(Rows.Count, 2).End(xlUp).Row 'Changed here
lc = ActiveSheet.UsedRange.Columns.Count
For i = lr To 2 Step -2
Range(Cells(i, 2), Cells(i, lc)).Copy Cells(i - 1, 2)
Rows(i).Delete
Next i
End Sub

HTH,
Bernie
MS Excel MVP

 

[JLatham]

LucyN,
What should things look like when it is done. I can read your request a
couple of ways, since you haven't said anything about Baton Rouge at all here.

1 Way:
BANGOR ME 54 54 45 45
deleting this row after moving up
BATON ROUGE LA 65 56 56 87
deleting this row after moving up, leaving:

BANGOR ME 54 54 45 45
BATON ROUGE LA 65 56 56 87

Or 2nd Way
BANGOR ME 54 54 45 45 65 56 56 87
and doing I have no idea what with Baton Rouge LA row.

Can we assume that the BANGOR ME, BATON ROUGE LA entries are in a single
cell and so the numbers below them can move directly up 1 row in the column
they are found in?

 

[LucyN]

The end result should look like the first way.

Thanks again, Lucy

 

[LucyN]

http://spreadsheets.google.com/ccc?key=pc2TfAeoe-8_tGIm5Msw1MQ&hl=en

I don't know if this helps, but that's a link to the document I am working
on from the Census website, transferred to Excel. The first couple lines of
the data sheet is what it is supposed to look like.

 

[JLatham]

That helped a lot. Always helps to see real information that will be used.

Try this code, worked for me for what I could copy from the sheet into an
Excel file here for testing. As it says, start by choosing one of the city
entries in column A where all below it (and itself) need to have data moves
made, then run this macro.

Sub MoveData()
'begin by selecting any city entry in column A
'that has the census info in the row below it
'THEN run this macro
'this code assumes that all entries from this point
'on are in need of the data moves.
Dim columnOffset As Integer
Application.ScreenUpdating = False ' makes it faster
Do Until IsEmpty(ActiveCell)
For columnOffset = 1 To 6 ' B through G
ActiveCell.Offset(0, columnOffset) = _
ActiveCell.Offset(1, columnOffset)
Next
ActiveCell.Offset(1, 0).EntireRow.Delete
ActiveCell.Offset(1, 0).Activate
Loop

End Sub

 

[LucyN]

That helped a lot. Always helps to see real information that will be used.

Try this code, worked for me for what I could copy from the sheet into an
Excel file here for testing. As it says, start by choosing one of the city
entries in column A where all below it (and itself) need to have data moves
made, then run this macro.

Sub MoveData()
'begin by selecting any city entry in column A
'that has the census info in the row below it
'THEN run this macro
'this code assumes that all entries from this point
'on are in need of the data moves.
Dim columnOffset As Integer
Application.ScreenUpdating = False ' makes it faster
Do Until IsEmpty(ActiveCell)
For columnOffset = 1 To 6 ' B through G
ActiveCell.Offset(0, columnOffset) = _
ActiveCell.Offset(1, columnOffset)
Next
ActiveCell.Offset(1, 0).EntireRow.Delete
ActiveCell.Offset(1, 0).Activate
Loop

End Sub

THANK YOU SOOOOOO MUCH!

 

[JLatham]

I take it that means it worked? Thanks for the feedback, glad I could
assist.