Fill Last Row in Column B to Last Row in Column A

ccalkins

New Member
Joined
Jul 13, 2016
Messages
6
Hi,
I am working on a spreadsheet utilizing a barcode scanner. Barcodes are scanned into Column A and when they are done being scanned I have a macro to fill in Column B with the scanner's initials and Column C with the date of the scan. I am running into issues if the scans are performed by a different person my current macro starts back over at B2:B & LastRowColumnA. I cannot figure out how to start the fill down starting at the last row in column B.
I have tried to inserting Dim LastRowColumnB As Long and trying to fit in LastRowColumnB:B and this is not the correct code. I will use whatever the appropriate method is to do the same fill with Column C. Any help would be greatly appreciated.

Screen Shot 2021-03-20 at 8.57.56 PM.png
Screen Shot 2021-03-20 at 8.58.10 PM.png
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,969
Office Version
  1. 2016
Platform
  1. Windows
When you post a picture of your code, we cannot copy/paste it into our copies of Excel in order to test out solutions for you. In order to do experiments with your code, we would have to type out all your code first. Not many people (including me) would be willing to do that. What you should do is copy/paste your code into your message, select it and click the VBA button on the message window's Ribbon. I'll give you a hint at the solution though... calculate the existing last row in Column B (using the same code idea you used to get the last row number in Column A) and concatenate it into the range addresses in place of the number 2 you are now using.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Try using
VBA Code:
Sub MM1()
Dim lrA As Long, lrB As Long, myvalue As String
lrA = Cells(Rows.Count, "A").End(xlUp).Row
lrB = Cells(Rows.Count, "B").End(xlUp).Row
myvalue = InputBox("Scanner Initials")
Range("B" & lrB & ":B" & lrA).Value = myvalue
Range("C2:C" & lrA).Value = Date
End Sub
 

ccalkins

New Member
Joined
Jul 13, 2016
Messages
6

ADVERTISEMENT

When you post a picture of your code, we cannot copy/paste it into our copies of Excel in order to test out solutions for you. In order to do experiments with your code, we would have to type out all your code first. Not many people (including me) would be willing to do that. What you should do is copy/paste your code into your message, select it and click the VBA button on the message window's Ribbon. I'll give you a hint at the solution though... calculate the existing last row in Column B (using the same code idea you used to get the last row number in Column A) and concatenate it into the range addresses in place of the number 2 you are now using.
Rick,
I was not sure how to post the code. Thank you for the advice, I will be sure to do that next time.
Craig
 

ccalkins

New Member
Joined
Jul 13, 2016
Messages
6
Try using
VBA Code:
Sub MM1()
Dim lrA As Long, lrB As Long, myvalue As String
lrA = Cells(Rows.Count, "A").End(xlUp).Row
lrB = Cells(Rows.Count, "B").End(xlUp).Row
myvalue = InputBox("Scanner Initials")
Range("B" & lrB & ":B" & lrA).Value = myvalue
Range("C2:C" & lrA).Value = Date
End Sub
Michael,
Thank you for your help! This code worked flawlessly. Thank you again,
Craig
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,969
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@Rick Rothstein
I was obviously bored..:cool:
It is the wee hours of Sunday morning here and I am no where near ready to go to sleep yet, so now I am bored. Okay, you know my penchant for compact code, especially one-liners, right? Well here you go...o_O
VBA Code:
Sub RR1()
  [A1].CurrentRegion.SpecialCells(xlBlanks) = Evaluate("{""" & InputBox("Scanner Initials") & """,""" & Date & """}")
End Sub

EDIT NOTE: If we assume the UsedRange on the active sheet is determined solely by the number of rows in Column A and the three columns with headers (in other words, there is no data, formulas or formatting beyond the last shown entry in Column A nor beyond Column C), then the above code line can be compacted even more...
VBA Code:
Sub RR2()
  [B:C].SpecialCells(xlBlanks) = Evaluate("{""" & InputBox("Scanner Initials") & """,""" & Date & """}")
End Sub
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
OMG... @Rick Rothstein
Always impressed by you compacting efforts.
The Evaluate function always seems to evade me.....and I struggle with the syntax. Is there a simple way of learning it properly ??
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,969
Office Version
  1. 2016
Platform
  1. Windows
Basically, Evaluate processes worksheet formulas given to it as a text string. The only thing you have to keep in mind is to double up internal quote marks (a requirement of VB's string processor). Also, Evaluate returns an array of values when ranges are processed so you can iterate those arrays if needed. Here all I did is create an array constant (that is what the added curly brackets do) of the scanner's initials and the current date (I used a comma delimiter, so the array constant will be two cells across a row) and assigned it to each of the two blank cells on every row till the end of the data. I'm not sure if that will be confusing or not but it is basically my thinking process in creating the code. If you have any question, please feel free to ask them.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Thanks @Rick Rothstein
I can follow what you have donein this thread, but as I mentioned, anytime I use the Evaluate function....I seem to always get the syntax wrong, even if it's a simple SUM.
I need to research more OR leave Eevaluate up to those far more understanding then me.. :ROFLMAO:
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

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
Top