Copy and paste date from one column to other except last row

Zain_inout

New Member
Joined
Sep 8, 2021
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I am working on a macro that will have different file to work with every time but all the files will have the same format as follows:

1) Row 1 will be merged from column A to Z
2) Row 2 will be merged from column A to Z
3) Row 3 will be merged from column A to Z
4) Row 4 will be blank
5) Row 5 will have headers
6) Data will start row 6 onwards
7) Last row will be merged from column A to Z and it will have the totals

Now, I would like to copy data from column A to column B except the last row because that is merged anyway. Below is my code:

Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
Range("A6").Select
ActiveSheet.Range("A6:A" & LR).Select
ActiveSheet.Range("A6:A" & LR).Copy Activesheet.Range("B6:B" & LR)
Application.CutCopyMode = False

Now, when I run this code, it is not doing anything because I think the last row is merged and that is causing the issue. Is there anyway I can still copy keeping the bottom most row merged and I need to write a code without giving any specific number of rows as the number of rows will be different every time in each file that the code will work on.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,936
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
HI

So if im understanding correct, just -1 from LR

as below, also think you had some code in there you did not need?

VBA Code:
Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count - 1
ActiveSheet.Range("A6:A" & LR).Copy Activesheet.Range("B6:B" & LR)
Application.CutCopyMode = False
 
Solution

Zain_inout

New Member
Joined
Sep 8, 2021
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
HI

So if im understanding correct, just -1 from LR

as below, also think you had some code in there you did not need?

VBA Code:
Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count - 1
ActiveSheet.Range("A6:A" & LR).Copy Activesheet.Range("B6:B" & LR)
Application.CutCopyMode = False
Hi, I tried this code but somehow it doesnt work :( may be because my last row is merged?
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,936
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Ok. Good.

strange why it needed -2, but if its working great. Just make sure your last cell is copying as expected, the one before the merged cell at the bottom

my preferred method fo Lastrow is below

VBA Code:
lr = Range("A" & Rows.Count).End(xlUp).Row
 

Forum statistics

Threads
1,148,189
Messages
5,745,241
Members
423,936
Latest member
Conservatopia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top