Finding last used cell

[Jared_Jones_23]

I have a macro that when its run it opens another workbook and selects specific data, based on conditions someone has on the original sheet, then copies the data to paste onto the original sheet. I have a code that works but when I try to reference the last cell on the sheet I am opening it finds the last row of the table which is 6600 instead of the last used row which is around 200. I will highlight where in the code the problem is, thank you for any ideas and help.

Sub Data()
Dim filenames As Variant
Dim macroname As String
Dim fnam, cse As String
Dim wbOpen As Workbook
Dim src_frow, src_lrow, tgt_frow, tgt_lrow, src_lcol, delcnt As Long
src_frow = 20
tgt_frow = 20
src_lcol = 138
cse = Range("D4").Value
delcnt = 0
macroname = "MACRO_COMBINE.xlsm"
fnam = ActiveWorkbook.Name
'If fnam <> macroname Then
' MsgBox "Please open " & macroname
' GoTo endout
'End If

Application.ScreenUpdating = False

filenames = Application.GetOpenFilename(, , , , True)
counter = 1
While counter <= UBound(filenames) ' ubound determines array size

Application.EnableEvents = False
Set wbOpen = Workbooks.Open(filenames(counter))
Application.EnableEvents = True

Sheets("Data").Select
Range("A20").Select
Selection.AutoFilter
ActiveSheet.rows.EntireRow.Hidden = False
ActiveSheet.Columns.EntireColumn.Hidden = False

If cse = "Total" Then
Call delbrows1(src_frow)
Else
Call delbrows2(src_frow, cse, delcnt)
End If

src_lrow = LastRowIndex(ActiveSheet, "B") 'LastRowIndex(ActiveSheet, 2)
Range(Cells(src_frow, 1), Cells(src_lrow, src_lcol)).Copy
Windows(macroname).Activate
tgt_lrow = src_lrow - src_frow + tgt_frow
Range(Cells(tgt_frow, 1), Cells(tgt_lrow, src_lcol)).PasteSpecial
Application.CutCopyMode = False
wbOpen.Close False
tgt_frow = tgt_lrow + 1
counter = counter + 1
cwrite = counter - 1
Wend

Call mod_cse(src_frow, cse, fname, cnt)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "SAM_" & cse & "_" & Format(Now, "yyyymmdd") & ".xlsx", FileFormat:=51
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox ("File saved as: " & ActiveWorkbook.Name _
& vbCr & vbCr)

endout:
End Sub
Sub delbrows1(fr)
Dim r As Long
For r = Cells(rows.Count, 2).End(xlUp).Row To fr Step -1
'For r = 1000 To 1 Step -1
If Cells(r, 2) = "" Then rows(r).Delete
Next r
End Sub
Sub delbrows2(fr, cse, delcnt)
Dim r As Long
For r = Cells(rows.Count, "B").End(xlUp).Row To fr Step -1
'For r = 1000 To 1 Step -1
If Cells(r, 8) <> cse Then
rows(r).Delete
delcnt = delcnt + 1
End If
Next r
End Sub
Public Function GetLastRowWithData() As Long
Dim ExcelLastCell As Object, lRow As Long, lLastDataRow As Long, l As Long
Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
lLastDataRow = ExcelLastCell.Row
lRow = ExcelLastCell.Row
Do While Application.CountA(ActiveSheet.rows(lRow)) = 0 And lRow <> 1
lRow = lRow - 1
Loop
lLastDataRow = lRow
GetLastRowWithData = lLastDataRow
End Function
Function LastRowIndex(ByVal w As Worksheet, ByVal col As Variant) As Long
LastRowIndex = .Cells(.rows.Count, "D").End(xlUp).Row
End Function
Sub mod_cse(src_frow, cse, fname, cnt)
ActiveSheet.Shapes("Button 17").Cut
'Range("a7:b18").ClearContents
'Range("c7").ClearContents
'Range("B7").Select
'With Selection.Validation
'.Delete
'End With
Range("B7").Value = cse
If cse = "Total" Then
Range("A7").Value = "PSR"
Else
Range("A7").Value = "Filtered"
End If
begin = 8
For c = 1 To cnt
myrow = begin + c
If myrow < 18 Then
Cells(myrow, 2) = fname(c)
Else
MsgBox "Too many files to document in list"
End If
Next c
Range("A20").Select
End Sub

 

[baitmaster]

Try using this approach instead. It will find that last actual used row. Its taken from a piece of my own code, so you will need to redefine variables, sheet names etc

lastOutputRow = targetWorksheet.Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row

If you go to the very bottom of column B, hold ctrl and hit UP arrow, where do you get to? If its row 6600, and my code also says 6600, its becuase that cell is actually being used, i.e. it contains data of some form, even if its null

In this case, you need to reconsider your definition of "last used" row, and adjust your code accordingly

 

[wigi]

And what is the contents of that cell B6600?

It could appear to be empty, yet have a space or some other character.

 

[Jared_Jones_23]

I tried the crt and up arrow and its saying my last row is 6399 which is what I mean earlier saying 6600, i was just estimating. However there is no content in the cell its completely blank with no formulas or conditional formats or anything. I think its just that its because of the table. Any ideas why this could be?
Thank you

 

[baitmaster]

Yep, its because its an XL07 table. Why not use the end of the table as your starting location? I just tested on one, and it works the same - just use your existing code from the table end

You can use your existing code twice if you want, once to find the table end, once to find the last data in it - assuming the last value is empty of course...

 

[Jared_Jones_23]

Sorry I am confused, are you saying that using my table size I can check for rows with only data in it? Or that I should just step through the table end all the way up since I know the end row?

 

[baitmaster]

Your current code Cells(rows.Count, "B").End(xlUp).Row finds the last cell in your table

Cells(rows.Count, "B").End(xlUp).End(xlUp).Row will find the last used cell in your table

The exception would be where your entire table contains data, it would return the first cell in the table. To prevent this, check if the last cell in the table (found using the code above) is empty or not