Code to copy formula to last row of data

U

usui

Board Regular
Joined
Apr 20, 2020
Messages
55
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hi Guys,

I'm trying to do a concatenate on some data that would base on how many data present on column A (header not included) and column B1-F1 (header not included), I tried recording a macro but unfortunately even though column A is only counts until A16(not fix number and may changes depends on data needed), I needed the cells starting from B2(fix)-F1000(can be change) and then remove the formula after (paste Value only).

The issue is since range was set to B2:F1000, the concatenate did not stop on the last data on column A (A16) and continued until it reach cell 1000.

I wanted a code that if concatenate reach the last data on column A (A16-can be change) will automatically stop there instead.

Sample here: I don't want the cells on yellow
1664436155947.png


Here is the code I got after recording macro, I changed the range until F1000 since data needed may changes on column A

Sub Concat()

ActiveCell.FormulaR1C1 = "=CONCATENATE(RC1,""-"",R1C)"
Range("B2").Select
Selection.Copy
Range("B2:F1000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

I hope someone can help modify this code or if anyone who has a better code instead, this should be the formula use: =CONCATENATE($A2,"-",B$1)
Thank you in advance
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Hi there

Please test the below and see if it does what you require?

VBA Code: 
Sub Concat()
    ActiveCell.Formula = "=IF(ISBLANK($A$1:$A$1000),"""",CONCATENATE($A2,""-"",B$1))"
    Range("B2").Select
    Selection.Copy
    Range("B2:F1000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub
 
Upvote 0
You will hopefully find the following useful. They are various ways of finding the bottom of your data.

VBA Code: 
Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
  LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

'Using SpecialCells Function
  LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).row

'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).row

'Using UsedRange
'  sht.UsedRange 'Refresh UsedRange
  LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).row

'Using Table Range
  LastRow = sht.ListObjects("SuppTbl").Range.Rows.Count

'Using Named Range
  LastRow = sht.Range("MyNamedRange").Rows.Count

'Ctrl + Shift + Down (Range should be first cell in data set)
  LastRow = sht.Range("A1").CurrentRegion.Rows.Count

Personally, after many years, I tend to use
sht.Cells.SpecialCells(xlCellTypeLastCell).row, then do a reverse loop (For i = Lastrow to 1 step -1) until a cell isn't blank. It copes with everything users have done.
 
Upvote 0
Jimmypop said:
Hi there

Please test the below and see if it does what you require?

VBA Code: 
Sub Concat()
    ActiveCell.Formula = "=IF(ISBLANK($A$1:$A$1000),"""",CONCATENATE($A2,""-"",B$1))"
    Range("B2").Select
    Selection.Copy
    Range("B2:F1000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub
Click to expand...
Hi Sir, it works but the issue is that I need to RUN the macro twice in order for it to work..1st RUN it just shows highlighted cells then if I RUN it again (2nd Run) then everything shows up and good.

Is this normal that I need to RUN it twice before the data appears?

1st Run:
1664440954596.png

2nd Run:
1664440992248.png
 
Upvote 0
Johnny C said:
You will hopefully find the following useful. They are various ways of finding the bottom of your data.

VBA Code: 
Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
  LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

'Using SpecialCells Function
  LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).row

'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).row

'Using UsedRange
'  sht.UsedRange 'Refresh UsedRange
  LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).row

'Using Table Range
  LastRow = sht.ListObjects("SuppTbl").Range.Rows.Count

'Using Named Range
  LastRow = sht.Range("MyNamedRange").Rows.Count

'Ctrl + Shift + Down (Range should be first cell in data set)
  LastRow = sht.Range("A1").CurrentRegion.Rows.Count

Personally, after many years, I tend to use
sht.Cells.SpecialCells(xlCellTypeLastCell).row, then do a reverse loop (For i = Lastrow to 1 step -1) until a cell isn't blank. It copes with everything users have done.
Click to expand...
Hi Sir, I think this has missing codes, can't run it
 
Upvote 0
What about
VBA Code: 
Sub Concat()
    With Range("B2:F" & Cells(Rows.Count, 6).End(xlUp).Row)
        .FormulaR1C1 = "=CONCATENATE(RC1,""-"",R1C)"
        .Value = .Value
    End With
End Sub
 
Upvote 0
mohadin said:
What about
VBA Code: 
Sub Concat()
    With Range("B2:F" & Cells(Rows.Count, 6).End(xlUp).Row)
        .FormulaR1C1 = "=CONCATENATE(RC1,""-"",R1C)"
        .Value = .Value
    End With
End Sub
Click to expand...
1664441457026.png

did not work sir, it messed up instead =(
 
Upvote 0
Try
VBA Code: 
Sub Concat()
    With Range("B2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    .Select
        .FormulaR1C1 = "=CONCATENATE(RC1,""-"",R1C)"
        .Value = .Value
    End With
End Sub
 
Upvote 0
Solution
Try this
VBA Code: 
Option Explicit
Sub usui_V1()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<< change to suit
   
    With ws.Range("B2:F" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
        .FormulaR1C1 = "=RC1&""-""&R1C"
        .Value = .Value
    End With
End Sub

Before
usui.xlsm
ABCDEF
1SquareWF-CAN-12x12WF-CAN-20x20WF-CAN-30x30WF-CAN-36x36WF-CAN-48x48
21
32
43
54
65
76
87
98
109
1110
1211
1312
1413
1514
1615
17
Sheet1


After
usui.xlsm
ABCDEF
1SquareWF-CAN-12x12WF-CAN-20x20WF-CAN-30x30WF-CAN-36x36WF-CAN-48x48
211 - WF-CAN-12x121 - WF-CAN-20x201 - WF-CAN-30x301 - WF-CAN-36x361 - WF-CAN-48x48
322 - WF-CAN-12x122 - WF-CAN-20x202 - WF-CAN-30x302 - WF-CAN-36x362 - WF-CAN-48x48
433 - WF-CAN-12x123 - WF-CAN-20x203 - WF-CAN-30x303 - WF-CAN-36x363 - WF-CAN-48x48
544 - WF-CAN-12x124 - WF-CAN-20x204 - WF-CAN-30x304 - WF-CAN-36x364 - WF-CAN-48x48
655 - WF-CAN-12x125 - WF-CAN-20x205 - WF-CAN-30x305 - WF-CAN-36x365 - WF-CAN-48x48
766 - WF-CAN-12x126 - WF-CAN-20x206 - WF-CAN-30x306 - WF-CAN-36x366 - WF-CAN-48x48
877 - WF-CAN-12x127 - WF-CAN-20x207 - WF-CAN-30x307 - WF-CAN-36x367 - WF-CAN-48x48
988 - WF-CAN-12x128 - WF-CAN-20x208 - WF-CAN-30x308 - WF-CAN-36x368 - WF-CAN-48x48
1099 - WF-CAN-12x129 - WF-CAN-20x209 - WF-CAN-30x309 - WF-CAN-36x369 - WF-CAN-48x48
111010 - WF-CAN-12x1210 - WF-CAN-20x2010 - WF-CAN-30x3010 - WF-CAN-36x3610 - WF-CAN-48x48
121111 - WF-CAN-12x1211 - WF-CAN-20x2011 - WF-CAN-30x3011 - WF-CAN-36x3611 - WF-CAN-48x48
131212 - WF-CAN-12x1212 - WF-CAN-20x2012 - WF-CAN-30x3012 - WF-CAN-36x3612 - WF-CAN-48x48
141313 - WF-CAN-12x1213 - WF-CAN-20x2013 - WF-CAN-30x3013 - WF-CAN-36x3613 - WF-CAN-48x48
151414 - WF-CAN-12x1214 - WF-CAN-20x2014 - WF-CAN-30x3014 - WF-CAN-36x3614 - WF-CAN-48x48
161515 - WF-CAN-12x1215 - WF-CAN-20x2015 - WF-CAN-30x3015 - WF-CAN-36x3615 - WF-CAN-48x48
17
Sheet1
 
Upvote 0
mohadin said:
Try
VBA Code: 
Sub Concat()
    With Range("B2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    .Select
        .FormulaR1C1 = "=CONCATENATE(RC1,""-"",R1C)"
        .Value = .Value
    End With
End Sub
Click to expand...
Wow works like magic sir. thank you so much
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
85
myall_blues
myall_blues
M
  • Question
Saveas newwb, close new wb, delete old wb
Replies
8
Views
226
eiloken
E
PIsabel
  • Question
Transpose columns to rows but without blanks
Replies
1
Views
191
bobaol
B
R
  • Solved
VBA Concatenate Issue
Replies
2
Views
332
Roballistic
R
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
331
kevin9999
K

Forum statistics

Threads
1,215,514
Messages
6,125,272
Members
449,219
Latest member
daynle

Share this page

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
Back
Top