How to Make Dynaminc Autofill and pasting as valuein VBA

[ygoyal578]

I'm trying to write a code such that it could work in dynamic range. But it is not executing. I have tried below code:

I'm trying to do:

1. Combine to columns (A and B) in to column C till last value in Column A. Then pasting the values of Column C in A and then Deleting column B and Column C.

Wanted to make it dynamic


Columns("C:C").Select
Range("C3").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C4").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&""_""&RC[-1]"
Range("C4").Select
Selection.AutoFill Destination:=Range("C4")
Range("C4:C4").Select
Selection.Copy
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:C").Select
Range("B3").Activate
Selection.Delete Shift:=xlToLeft
Range("A3").Select

 

[Joe4]

Try this:

Sub MyCombine()

    Dim lr As Long
    
    Application.ScreenUpdating = False
    
'   Insert a blank column at column C
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Write formulas to combine A and B in column C, starting at row 4
    Range("C4:C" & lr).Formula2R1C1 = "=RC[-2]&""_""&RC[-1]"
    
'   Make formulas into hard-coded values
    Range("C4:C" & lr).Value = Range("C4:C" & lr).Value
    
'   Delete columns A and B
    Columns("A:B").Delete
    
    Application.ScreenUpdating = True

End Sub

 

[ygoyal578]

thanks

Try this:

Sub MyCombine()

    Dim lr As Long
   
    Application.ScreenUpdating = False
   
'   Insert a blank column at column C
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Write formulas to combine A and B in column C, starting at row 4
    Range("C4:C" & lr).Formula2R1C1 = "=RC[-2]&""_""&RC[-1]"
   
'   Make formulas into hard-coded values
    Range("C4:C" & lr).Value = Range("C4:C" & lr).Value
   
'   Delete columns A and B
    Columns("A:B").Delete
   
    Application.ScreenUpdating = True

End Sub

thanks

 

[Joe4]

You are welcome.