Copy down process - how to translate it into macro

InnaG

New Member
Joined
Mar 18, 2019
Messages
19
Hello,
In my office we often get excel files with merged cells. when we unmerge them - there blank cells. To fix this problem, before we import information into the system, we do the following manual process:

Highlight columns that need to be copied down
Hit Ctrl+G
Special
Blanks
OK

In the cell type
type = then arrow up

Hit Ctrl+Enter key

this will copy everything above down in the columns
Then highlight entire file, copy and paste special - values - enter.

I tried to records macro, but the problem is we never know how many columns we will be using.

Here is code from recorded macro:


CopyDown()


Columns("A:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose: =False

End Sub

Any advise?

Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
You can dynamically find the last row and column, and use that.
So replace this:
Code:
[COLOR=#333333]Columns("A:B").Select[/COLOR]
with this:
Code:
    Dim lrow As Long
    Dim lcol As Long
    
    lrow = Range("A1").SpecialCells(xlLastCell).Row
    lcol = Range("A1").SpecialCells(xlLastCell).Column
    
    Range(Cells(1, 1), Cells(lrow, lcol)).Select
 

InnaG

New Member
Joined
Mar 18, 2019
Messages
19
Thank you but I have a question, when we use this formula we do not use it on the utilization columns (numbers and dollar amounts). Some files have them some don't.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
Then please explain the logic we would need to use (in plain English) for deciding which columns to include.
Are there certain headers to include/not include?
 

InnaG

New Member
Joined
Mar 18, 2019
Messages
19
they are different every time. I am very new to macros and vba, but is there a way to create a msg box where techs can input the columns they need to update?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
Yes, you can do something like this:
Code:
[COLOR=#333333]Sub CopyDown()[/COLOR]

Dim col as String

col = InputBox("Please enter the last column")

[COLOR=#333333]Columns("A:" & col).Select[/COLOR]
[COLOR=#333333]Selection.SpecialCells(xlCellTypeBlanks).Select[/COLOR]
[COLOR=#333333]Selection.FormulaR1C1 = "=R[-1]C"[/COLOR]
[COLOR=#333333]Cells.Select[/COLOR]
[COLOR=#333333]Selection.Copy[/COLOR]
[COLOR=#333333]Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#333333]:=False, Transpose: =False[/COLOR]

[COLOR=#333333]End Sub[/COLOR]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
You are welcome.
 

Forum statistics

Threads
1,085,514
Messages
5,384,124
Members
401,881
Latest member
Dato

Some videos you may like

This Week's Hot Topics

Top