| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
select last cell in a dynamic list using a macro
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
select last cell in a dynamic list using a macro
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
select last cell in a dynamic list using a macro
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
select last cell in a dynamic list using a macro
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| insert date | Excel Worksheet Functions | |||
| select list by selecting a cell | Excel Worksheet Functions | |||
| Row Expansion | Excel Worksheet Functions | |||
| Restarting a macro | Excel Worksheet Functions | |||
| Select one variable from a list of variables by clicking one cell | Excel Discussion (Misc queries) | |||
Linear Mode
