VBA End Row

[SandMan86]

All,

I know there are several help topics for VBA end row -- but, I'm still relatively new to VBA, and I'm not sure if I can insert one of them into the macro that I've recorded to automate one of my daily reports.

I generate a report each day that summarizes all of the data M-T-D. Instead of adding each successive day, I run it fresh each day to ensure that everything gets exported from our billing/invoicing system. That being said, I take the data and format it as a pivot table because the end user prefers to see it that way. So, when I record the macro to create the pivot table, it records it with absolute reference. Here's the code that it's using for that:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$K$830"), , xlYes).Name = _
"Table1"

Does anyone know if there is a way I can edit this to select all contigious rows with data as opposed to a defined range of cells?

Thanks in advance!

 

[Soggy]

You can use something like this to determine the last row.

Define a variable a tthe top of your code.

dim lstrow as long

Then this will work out the last row of used data based on column A

lstrow = activesheet.Cells(Rows.Count, "A").End(xlUp).Row

Then when you are setting the range for the pivot the variable is called. notice how it needs to be outside the " with an & to link

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$K" & lstrow), , xlYes).Name = "Table1"

Hope this helps. I haven't tested so make sure you check it but any questions let me know.

Cheers

soggy

 

[L. Howard]

Maybe you can adapt something like this into you code.

Option Explicit

Sub AllMyRows()
  Dim Rng As Range
  Set Rng = Range("A1", Range("A1").End(xlDown))
  Rng.Select
End Sub

Regards,
Howard