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

ccalkins

New Member
Joined
Jul 13, 2016
Messages
13
Office Version
  1. 365
Platform
  1. MacOS
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
@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:
Upvote 0
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 ??
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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