Using Macro to Copy/Paste

[LucyN]

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

 

[John C]

Post your macro code, and we can modify.

 

[LucyN]

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

 

[Dave Peterson]

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

 

[Roger Govier]

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

 

[LucyN]

Thank you all! =)