Forum Discussion
using VBA to select first empty row.
Hi Katrina,
I'll point you in this direction and give a few tips
Check out this bit of code
Sub FindLastCell()
Dim LastCell As Range
Dim LastCellColRef As Long
LastCellColRef = 2 'column number to look in when finding last cell
Set LastCell = Sheets("UNNEEDED").Cells(Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)
MsgBox LastCell.Address
Set LastCell = Nothing
End Sub
The first change I would make to this code is to replace the Sheets("UNNEEDED") part with the underlying sheet number e.g Sheet2.cells or Sheet3.cells You can see that sheet number next to the name in the VBA editor window. The main reason is to prevent your code breaking if the sheet is renamed.
The Dim part is setting the variables to use in your code. This is a very important concept to learn about when writing VBA.
I've made LastCellColRef as a variable as this makes it clear that it is column 2 that is being searched in to find the last entry
Once you've SET your LastCell you can use it as a reference in your code
e.g.
Selection.Copy
LastCell.PasteSpecial xlPasteValues
Tips
Once you've set a variable, then at the end of your routine you should set it = nothing to clear it from memory.
Try to avoid cell referencing when writing VBA e.g. Range("$A$2:$DM$8442), use named ranges instead.
I know very little about programming in any language. I tried putting this into the code/macro and still got an compile error. Where am i supposed to put this code? The VBA editor you mentioned, is that the same thing that you go to when you click the "step into" function on a macro?
I get the concept, basically creating and setting up a new word within the VBA database right?
Hi Katrina,
I've edited your code below to hopefully something that works.
I'd strongly recommend learning about the key elements of VBA if you are intending to write code, you can get yourself into all sorts of complications if you don't start with the basics.
Here's a few tips in an article I wrote plus links to resources
https://www.linkedin.com/pulse/why-do-macros-have-bad-reputation-wyn-hopkins/
Sub RPL_SORT2() ' ' RPL_SORT2 Macro ' SORTS OUT ANYTHING WITH AMOUNT OF "0" TO ORDER ' Dim LastCell As Range Dim LastCellColRef As Long LastCellColRef = 1 'column number to look in when finding last cell on destination sheet - change this as needed Set LastCell = Sheets("UNNEEDED").Cells(Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0) ' Rows("2:2").Select Selection.AutoFilter ActiveSheet.Range("$A$2:$DM$8442").AutoFilter Field:=6, Criteria1:="<1", _ Operator:=xlAnd Rows("3:3").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy LastCell.PasteSpecial xlPasteValues Sheets("ALL").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp ActiveSheet.AutoFilterMode = OFF Set LastCell = Nothing End Sub- ... yep the VBA Editor is the place you go when you click Step Into. The keyboard shortcut is Alt+F11
Thank you. I read your article and i have a book on the way but have not received it yet.
