Zain_inout
New Member
- Joined
- Sep 8, 2021
- Messages
- 18
- Office Version
- 2019
- Platform
- 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.
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.