Find last row of data in cell A that is a part of a table

[brianv]

I have a VBA code to find the last row of data in cell A

Range("A65535").End(xlUp).Offset(1, 0).Select

'Copy 1st Blank Row after Data and Paste/Insert
ActiveCell.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

But i know need to convert Cells A9:CI72 into a table. But the code above will treat the whole table as data even if row 68 is blank. so it inserts the data i want into row 73, which is outside the table.

The intent of this portion of the code was to find last row of data, go down 1 row, copy that row, re-insert it, then paste the new data into that same row, thereby extending the table.

How do i change the find the last row of actual data and ignore the table?

 

[Alex Blakenburg]

See if this is what you are after. We wouldn't normally use Select (or Activate) but I think that is what you need to make the rest of your code work.
You will need to change Table1 in the code to your table name.

Sub FindLastRowInTable()

    Dim tbl As ListObject
    Dim CellLastRow As Range
    
    Set tbl = Range("Table1").ListObject             '<--- change table name to your table name
    ' Make sure no filters are active
    If tbl.ShowAutoFilter = True Then
        tbl.AutoFilter.ShowAllData
    End If

    ' Select Last Row plus 1 in column 1
    Set CellLastRow = tbl.ListColumns(1).Range.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    CellLastRow.Offset(1).Select
End Sub

 

[brianv]

I Ales,
I think that is the gist of it, I placed this in mod2 ran it from there it worked, but it appears i cant embed it into the original script, so how do i run this sub while inside the original script?

The section I snapshot'd in the OP was from the code inside a UserForm called "CmdSubmit_Click"

Thank You!

 

[Alex Blakenburg]

What happens if in your form sub you put
Call FindLastRowInTable
(With that sub still in mod2)

 

[brianv]

Thx, i essentially replaced my previous find last row command line item are replaced with the Call.

'Find the Last row INSIDE of the ProjectData Table
'Range("A65535").End(xlUp).Offset(1, 0).Select <---(This line is for a non-table version of hte Project data)
Call FindLastRowInTable

'Then Copy 1st Blank Row after Data and Paste/Insert
ActiveCell.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

But after testing, the FindLastRowInTable still tracks to below the table, inserting on Row71 as shown below:

But if i run the FindLastRowInTable by itself, it does track to the proper location A66.

 

[Alex Blakenburg]

Any chance you can share your workbook via Google drive or Dropbox etc ?
You would need to make available to anyone with the link and post the link.

 

[brianv]

I applied a workaround actually, i shifted the entire table 1 column right to start in Column B and not A. Then i included a line in the script to add text to the same inserted row in Column A (Cell A65 "Yes" in the above image), so that the find last column is always looking at column A raw text and not data within a table. I just then hide column A. Simple solution, works great.

I appreciate your efforts!

 

[Alex Blakenburg]

Thanks for letting us know. Glad you found something that worked for you.

 

[brianv]

Separate question, I have a script in WB1 to load data into WB2 (Same workbook as mentioned above), but then i would like that WB1 to also start/run a macro in WB2 (the script mention above to load the data into the table).

Sub UploadtoPMFLowLog()

    Dim FLPath As String
    Dim WB As Workbook
    Dim WB1 As Workbook
  
    'Capture current workbook
    Set WB1 = ActiveWorkbook
    
    'Copy Booking data from BookingFile
    Sheets("Admin").Visible = True
    Sheets("Admin").Select
    Columns("AB:AB").Select
    Selection.Copy
    Range("A1").Select
    
    ' Get the FlowLog file path from the cell
    FLPath = Range("AG29").Value
    ' Check if the file exists
    If Dir(FLPath) <> "" Then
    ' Open the workbook
    Set WB = Workbooks.Open(FLPath)

    'Paste data into PM FLow Log File
    Sheets("Admin").Visible = True
    Sheets("Admin").Select
    Columns("T:T").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Sheets("Dashboard").Select
    Sheets("Admin").Visible = False
    Range("I5").Select
    
    'Start the New Entry User Form
    Application.Run "'CF PM Project Flow Log.xlsm'!NewEntry_Click"
    
    'Go back to Booking File workbook
    WB1.Activate
    Application.CutCopyMode = False
    Sheets("Admin").Visible = False
    Sheets("Booking").Select
    Range("P2").Select
  
    'Go back to PM FLowLog workbook
    WB.Activate
            
    Else
        MsgBox "File not found!"
    End If
End Sub

I attempted to the Application.Run "CF PM Project Flow Log.xlsm'!NewEntry_Click" in the WB1 script, but that is not working.

[FLPath = Range("AG29").Value] is referencing the same file "CF PM Project Flow Log.xlsm"

What am i missing?

Thanks
BV

 

[Alex Blakenburg]

I'm afraid that's a very different question and will need a new thread.