select last cell in a dynamic list using a macro

Hello,
I am trying to select a range of cells using a macro. The range grows each
day starting in cell B5 and ending in H* (* = the row with the last day's
worth of data). Two lines down, there is a grand total line, and two rows
from that is a footer of sorts, with date and a page number.

I found a way to get to the last used cell using a macro, however I do not
want the last cell. I want the last cell minus 4 rows.

Also, I am not sure how to select the entire range from B5 to H*. I am able
to select one cell or the other, but not the entire range.

I only dabble in VBA and have not had any program training. Any help that
anyone can provide will be greatly appreciated!

Let me know if you need more details.

Thanks in advance!

Try something like this

lrow = Cells(Rows.Count, "H").End(xlUp).Row
Debug.Print lrow
You can add the code to ensure that the last row # is > 4 I suspect.

"uncrox" wrote:

> Hello,
> I am trying to select a range of cells using a macro. The range grows each
> day starting in cell B5 and ending in H* (* = the row with the last day's
> worth of data). Two lines down, there is a grand total line, and two rows
> from that is a footer of sorts, with date and a page number.
>
> I found a way to get to the last used cell using a macro, however I do not
> want the last cell. I want the last cell minus 4 rows.
>
> Also, I am not sure how to select the entire range from B5 to H*. I am able
> to select one cell or the other, but not the entire range.
>
> I only dabble in VBA and have not had any program training. Any help that
> anyone can provide will be greatly appreciated!
>
> Let me know if you need more details.
>
> Thanks in advance!
>

Barb,
Thanks, but I'm not sure what to do with this now. I tried just inserting
the code into mine, but it didn't seem to do anything. I'm still not sure how
to tell it to select.

Here is my code:

Columns("B:B").Select
Selection.ColumnWidth = 20.86
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
Columns("E:G").Select
Selection.Delete Shift:=xlToLeft
Columns("C:H").Select
Selection.ColumnWidth = 8
lrow = Cells(Rows.Count, "H").End(xlUp).Row
Debug.Print lrow
Range("B5").Select

Using the code you gave me, how do I tell it to highlight the entire range
from B5 to Hlrow?

Thanks,


"Barb Reinhardt" wrote:

> Try something like this
>
> lrow = Cells(Rows.Count, "H").End(xlUp).Row
> Debug.Print lrow
> You can add the code to ensure that the last row # is > 4 I suspect.
>
> "uncrox" wrote:
>
> > Hello,
> > I am trying to select a range of cells using a macro. The range grows each
> > day starting in cell B5 and ending in H* (* = the row with the last day's
> > worth of data). Two lines down, there is a grand total line, and two rows
> > from that is a footer of sorts, with date and a page number.
> >
> > I found a way to get to the last used cell using a macro, however I do not
> > want the last cell. I want the last cell minus 4 rows.
> >
> > Also, I am not sure how to select the entire range from B5 to H*. I am able
> > to select one cell or the other, but not the entire range.
> >
> > I only dabble in VBA and have not had any program training. Any help that
> > anyone can provide will be greatly appreciated!
> >
> > Let me know if you need more details.
> >
> > Thanks in advance!
> >

Change
Range("B5").Select
to
Range("B5:H" & lrow ).Select

"uncrox" wrote:

> Barb,
> Thanks, but I'm not sure what to do with this now. I tried just inserting
> the code into mine, but it didn't seem to do anything. I'm still not sure how
> to tell it to select.
>
> Here is my code:
>
> Columns("B:B").Select
> Selection.ColumnWidth = 20.86
> Cells.Select
> Cells.EntireColumn.AutoFit
> Cells.EntireRow.AutoFit
> Columns("E:G").Select
> Selection.Delete Shift:=xlToLeft
> Columns("C:H").Select
> Selection.ColumnWidth = 8
> lrow = Cells(Rows.Count, "H").End(xlUp).Row
> Debug.Print lrow
> Range("B5").Select
>
> Using the code you gave me, how do I tell it to highlight the entire range
> from B5 to Hlrow?
>
> Thanks,
>
>
> "Barb Reinhardt" wrote:
>
> > Try something like this
> >
> > lrow = Cells(Rows.Count, "H").End(xlUp).Row
> > Debug.Print lrow
> > You can add the code to ensure that the last row # is > 4 I suspect.
> >
> > "uncrox" wrote:
> >
> > > Hello,
> > > I am trying to select a range of cells using a macro. The range grows each
> > > day starting in cell B5 and ending in H* (* = the row with the last day's
> > > worth of data). Two lines down, there is a grand total line, and two rows
> > > from that is a footer of sorts, with date and a page number.
> > >
> > > I found a way to get to the last used cell using a macro, however I do not
> > > want the last cell. I want the last cell minus 4 rows.
> > >
> > > Also, I am not sure how to select the entire range from B5 to H*. I am able
> > > to select one cell or the other, but not the entire range.
> > >
> > > I only dabble in VBA and have not had any program training. Any help that
> > > anyone can provide will be greatly appreciated!
> > >
> > > Let me know if you need more details.
> > >
> > > Thanks in advance!
> > >

Thanks Barb! That worked great!


"Barb Reinhardt" wrote:

> Change
> Range("B5").Select
> to
> Range("B5:H" & lrow ).Select
>
> "uncrox" wrote:
>
> > Barb,
> > Thanks, but I'm not sure what to do with this now. I tried just inserting
> > the code into mine, but it didn't seem to do anything. I'm still not sure how
> > to tell it to select.
> >
> > Here is my code:
> >
> > Columns("B:B").Select
> > Selection.ColumnWidth = 20.86
> > Cells.Select
> > Cells.EntireColumn.AutoFit
> > Cells.EntireRow.AutoFit
> > Columns("E:G").Select
> > Selection.Delete Shift:=xlToLeft
> > Columns("C:H").Select
> > Selection.ColumnWidth = 8
> > lrow = Cells(Rows.Count, "H").End(xlUp).Row
> > Debug.Print lrow
> > Range("B5").Select
> >
> > Using the code you gave me, how do I tell it to highlight the entire range
> > from B5 to Hlrow?
> >
> > Thanks,
> >
> >
> > "Barb Reinhardt" wrote:
> >
> > > Try something like this
> > >
> > > lrow = Cells(Rows.Count, "H").End(xlUp).Row
> > > Debug.Print lrow
> > > You can add the code to ensure that the last row # is > 4 I suspect.
> > >
> > > "uncrox" wrote:
> > >
> > > > Hello,
> > > > I am trying to select a range of cells using a macro. The range grows each
> > > > day starting in cell B5 and ending in H* (* = the row with the last day's
> > > > worth of data). Two lines down, there is a grand total line, and two rows
> > > > from that is a footer of sorts, with date and a page number.
> > > >
> > > > I found a way to get to the last used cell using a macro, however I do not
> > > > want the last cell. I want the last cell minus 4 rows.
> > > >
> > > > Also, I am not sure how to select the entire range from B5 to H*. I am able
> > > > to select one cell or the other, but not the entire range.
> > > >
> > > > I only dabble in VBA and have not had any program training. Any help that
> > > > anyone can provide will be greatly appreciated!
> > > >
> > > > Let me know if you need more details.
> > > >
> > > > Thanks in advance!
> > > >