Macro to Concatenate

[ldashev]

I'm working on a pretty simple Macro in excel to delete several columns in excel. There are 10 columns and 5 are deleted. The last step of the of the Macro is to Concatenate 3 cells and then copy the formula down.

My challenge is let's say this month there will be 100 records, while next month there will be 200 and the month after 150. How can I build the Macro to automatically stop copying the cells down, when there is empty cells. (cut off)

Here's the exact code:
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[1],""-"",TEXT(RC[3],""m/d/yyyy h:mm""),""-"",TEXT(RC[4],""m/d/yyyy h:mm""))"
Range("A2").Select
Columns("A:A").EntireColumn.AutoFit
Range("A2").Select
Selection.Copy
Range("A3:A16312").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=3
Range("A15177:A16312").Select
Range("A16312").Activate
Application.CutCopyMode = False
Selection.ClearContents
Range("B15180").Select
ActiveWindow.ScrollRow = 15145
ActiveWindow.ScrollRow = 15122
ActiveWindow.ScrollRow = 15100
ActiveWindow.ScrollRow = 15034
ActiveWindow.ScrollRow = 14900
ActiveWindow.ScrollRow = 14656
ActiveWindow.ScrollRow = 14390
ActiveWindow.ScrollRow = 13968
ActiveWindow.ScrollRow = 13391
ActiveWindow.ScrollRow = 12680
ActiveWindow.ScrollRow = 11992
ActiveWindow.ScrollRow = 11148
ActiveWindow.ScrollRow = 10393
ActiveWindow.ScrollRow = 9660
ActiveWindow.ScrollRow = 8906
ActiveWindow.ScrollRow = 8239
ActiveWindow.ScrollRow = 7551
ActiveWindow.ScrollRow = 7063
ActiveWindow.ScrollRow = 6619
ActiveWindow.ScrollRow = 6241
ActiveWindow.ScrollRow = 5864
ActiveWindow.ScrollRow = 5531
ActiveWindow.ScrollRow = 5286
ActiveWindow.ScrollRow = 5064
ActiveWindow.ScrollRow = 4842
ActiveWindow.ScrollRow = 4665
ActiveWindow.ScrollRow = 4509
ActiveWindow.ScrollRow = 4376
ActiveWindow.ScrollRow = 4221
ActiveWindow.ScrollRow = 4065
ActiveWindow.ScrollRow = 3976
ActiveWindow.ScrollRow = 3843
ActiveWindow.ScrollRow = 3754
ActiveWindow.ScrollRow = 3666
ActiveWindow.ScrollRow = 3532
ActiveWindow.ScrollRow = 3466
ActiveWindow.ScrollRow = 3399
ActiveWindow.ScrollRow = 3310
ActiveWindow.ScrollRow = 3221
ActiveWindow.ScrollRow = 3133
ActiveWindow.ScrollRow = 2955
ActiveWindow.ScrollRow = 2844
ActiveWindow.ScrollRow = 2711
ActiveWindow.ScrollRow = 2555
ActiveWindow.ScrollRow = 2333
ActiveWindow.ScrollRow = 2200
ActiveWindow.ScrollRow = 2000
ActiveWindow.ScrollRow = 1778
ActiveWindow.ScrollRow = 1623
ActiveWindow.ScrollRow = 1445
ActiveWindow.ScrollRow = 1290
ActiveWindow.ScrollRow = 1179
ActiveWindow.ScrollRow = 1068
ActiveWindow.ScrollRow = 979
ActiveWindow.ScrollRow = 868
ActiveWindow.ScrollRow = 735
ActiveWindow.ScrollRow = 646
ActiveWindow.ScrollRow = 601
ActiveWindow.ScrollRow = 446
ActiveWindow.ScrollRow = 357
ActiveWindow.ScrollRow = 246
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 2
Range("A1").Select

 

[thes4s67]

You can count the last row with something like this:

Dim LR as Integer
LR = Sheets("Sheet1").Cells(Rows.count, "A").End(xlUp).Row

'Then use...
Range("A3:A" & LR)

 

[L. Howard]

Maybe this will help. It does not represent the cells/ranges you are showing in your post, but is a method to enter formulas in a variable column range.

Where here, it uses column A to find how many rows to copy the formula down to and column D, cell D1 resized, to match the rows in column A for the formulas.

The : .Value = .Value part will return the values of the formulas (no formulas will show in the cells). If you want the formulas to be in the cells, then delete the : .Value = .Value from the code.

This method fills thousands of rows with formula (or their values) in a blink.

Sub myLenColumn()

  Dim lRowCount&
 
  lRowCount = Cells(Rows.Count, "A").End(xlUp).Row ''Note is column A for row count
  
  With Range("D1").Resize(lRowCount)  'Returning values in column D
    .Formula = "=LEFT(A1,LEN(A1)-1)": .Value = .Value
  End With

End Sub

Howard