Hi,
I need help with a Macro on Excel. I have a large data set. And I want to
move the numbers up to Bangor and delete the space between the two. The moves
the next set of numbers up to Baton Rouge, and delete the space after that.
(if that makes any sense?). When I record the macro and replay, it just keeps
going to the original cell. Thanks a lot!

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

Post your macro code, and we can modify.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"LucyN" wrote:

Hi,
I need help with a Macro on Excel. I have a large data set. And I want to
move the numbers up to Bangor and delete the space between the two. The moves
the next set of numbers up to Baton Rouge, and delete the space after that.
(if that makes any sense?). When I record the macro and replay, it just keeps
going to the original cell. Thanks a lot!

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

I'm not sure if that helps, first time using macros. Thanks for all the help!

Keyboard Shortcut: Ctrl+b

Range("B17:G17").Select
Selection.Copy
Range("B16").Select
ActiveSheet.Paste
Rows("17:17").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("B18:G18").Select
Selection.Copy
Range("B17").Select
ActiveSheet.Paste
Rows("18:18").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("B19:G19").Select
Selection.Copy
Range("B18").Select
ActiveSheet.Paste
Rows("19:19").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
End Sub

Option Explicit
Sub testme01()
Dim wks As Worksheet
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 1 'no headers
'lastrow is one more than the last row in column A
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

For iRow = LastRow To FirstRow + 1 Step -2
'b:g is 6 columns, so .resize(1,6)
.Cells(iRow, "B").Resize(1, 6).Copy _
Destination:=.Cells(iRow - 1, "b")
.Rows(iRow).Delete
Next iRow
End With
End Sub



LucyN wrote:

Hi,
I need help with a Macro on Excel. I have a large data set. And I want to
move the numbers up to Bangor and delete the space between the two. The moves
the next set of numbers up to Baton Rouge, and delete the space after that.
(if that makes any sense?). When I record the macro and replay, it just keeps
going to the original cell. Thanks a lot!

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









--

Dave Peterson

Hi Lucy

Replace your macro with the following code

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

--
Regards
Roger Govier

"LucyN" wrote in message
...
I'm not sure if that helps, first time using macros. Thanks for all the
help!

Keyboard Shortcut: Ctrl+b

Range("B17:G17").Select
Selection.Copy
Range("B16").Select
ActiveSheet.Paste
Rows("17:17").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("B18:G18").Select
Selection.Copy
Range("B17").Select
ActiveSheet.Paste
Rows("18:18").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("B19:G19").Select
Selection.Copy
Range("B18").Select
ActiveSheet.Paste
Rows("19:19").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
End Sub

Thank you all! =)