Forum Discussion

katrina bethea's avatar
katrina bethea
Brass Contributor
Nov 02, 2018

using VBA to select first empty row.

I am teaching myself how to work all aspects of excel (formulas, Macros/VBA, ect) on my own with no books and online free resources only. I say this because some of my questions may have an obvious fix to you but for me it is a problem i haven't encountered before. given that my current dilema is as follows;

 

I am trying to find a VBA code that i can insert into a current recorded macro to select the first empty row before pasting copied information. IE; my current macro has "Range("A68").select", but if an item is added "A68" would no longer be the last empty cell/row.

 

 

i found this code online;

  • Sub selectlastemptyrow()
  • Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
  • End Sub
This code results in error "compile error: expected end sub".
 
 
Tried changing the code to (keep in mind i have no idea what i'm doing, just trying to learn);
  • ActiveSheet.selectlastemptyrow(Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)).Select
I've messed with different ways to write this and i get the same error "object doesn't support this property or method".
 
 
the following is the macro im trying to insert this into; 
  • Sub RPL_SORT2()
    '
    ' RPL_SORT2 Macro
    ' SORTS OUT ANYTHING WITH AMOUNT OF "0" TO ORDER
    '

    '
    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
    Sheets("UNNEEDED").Select
    Rows("3:3").Select "this row and the following 2 rows of code are being replaced"
    ActiveWindow.SmallScroll Down:=2
    Range("A68").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=3
    Sheets("ALL").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    ActiveSheet.AutoFilterMode = OFF
    End Sub

Again thank you all for the help and information. I'm not just looking for someone to fix my problems but im trying to learn too so if you could give a simple explanation of how to get this to work with or without a working version of the needed code i would greatly appreciate it.

 
Macros and VBA
  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Nov 02, 2018

    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.

     

     

    • katrina bethea's avatar
      katrina bethea
      Brass Contributor
      Nov 05, 2018

      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? 

      • Wyn Hopkins's avatar
        Wyn Hopkins
        MVP
        Nov 06, 2018

        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

         

Resources