Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
Hi!
Is it possible for a macro to format the last row from say A-H in a worksheet. I can't do this manually since the length of the worksheet changes from time to time. Regards Calle |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
This should find the last cell in Col A that has an entry. Then changes the
font in column A-H to bold for that row. Modify to use whatever formatting you wanted. Sub test() With Worksheets("Sheet2") With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub "Calle" wrote: Hi! Is it possible for a macro to format the last row from say A-H in a worksheet. I can't do this manually since the length of the worksheet changes from time to time. Regards Calle |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
Thx! Is there anyway to get it to ignore if tehre is a formula in that cell?
Regards Calle |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
If which cell has a formula? If the cell in column A
Sub test() With Worksheets("Sheet2") With .Cells(.Rows.Count, 1).End(xlUp) If Not .HasFormula Then With .Resize(1, 8) .Font.Bold = True End With End If End With End With End Sub If you're talking about the cells in Column A-H, format the ones w/o formulas and skip those w/formulas, then try: Sub test() Dim rngCell As Range With Worksheets("Sheet2") For Each rngCell In .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) If Not rngCell.HasFormula Then rngCell.Font.Bold = True End If Next rngCell End With End Sub Or do mean something else? "Calle" wrote: Thx! Is there anyway to get it to ignore if tehre is a formula in that cell? Regards Calle |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
I meant the row not cell, sorry. The last 50 cells on that sheet have
forumlas but are somtime "empty" exept for the formula of course. So sometimes I need the format change to occur on rows that has formulas in them ( but looks empty) . Anyway I will try your scrpit. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
It didn't work. The macro still wont regard the empty cells with only
formulas in them as empty. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
I'm confused.
So sometimes I need the format change to occur on rows that has formulas in them ( but looks empty) Is there anyway to get it to ignore if tehre is a formula in that cell? The macro still wont regard the empty cells with only formulas in them as empty. You will need to explain further. I've got there are formulas in your data that may/may not be blank ( I assume IF statements returning ""). Are you looking to format the last row that has at least one cell with non-blank data or the last row with all blanks? Are there only empty cells below your data? "Calle" wrote: It didn't work. The macro still wont regard the empty cells with only formulas in them as empty. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
A cell can look/be empty but till have a formula e.g "". I want to count
those cells as empty... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
What are the specific conditions under which you want the row formatted? The
last row where all of the cells are non-blank or at least one cell has a value. 1 2 3 "" 3 "" "" "" "" With "" being cells w/formulae that are displaying blank. Which row do you want formatted (with the second row being *truly* empty). "Calle" wrote: A cell can look/be empty but till have a formula e.g "". I want to count those cells as empty... |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
JMB;
I have tried this code and I get an error and it goes into debug mode. I have typed the macro exactly as you have it. I did change Sheet2 to Sheet1. As a side note, how would I default this to the active sheet? Thanks - Bruce -- VBA Rookie "JMB" wrote: This should find the last cell in Col A that has an entry. Then changes the font in column A-H to bold for that row. Modify to use whatever formatting you wanted. Sub test() With Worksheets("Sheet2") With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub "Calle" wrote: Hi! Is it possible for a macro to format the last row from say A-H in a worksheet. I can't do this manually since the length of the worksheet changes from time to time. Regards Calle |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
To work with the activesheet - you can qualify it
Sub test() With ActiveSheet With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub or just use (the post will likely get wrapped funny - there is three lines in the following macro) and VBA will assume the activesheet Sub test() Cells(Rows.Count, 1).End(xlUp).Resize(1, 8).Font.Bold = True End Sub You would have to copy the code from your macro into a post before I can try to figure out why it is not working (and the error description and which line it breaks on would also help) - it runs okay for me. Note there is a period in front of Cells and Font for the macro(s) that have a With statement. "Bruce" wrote: JMB; I have tried this code and I get an error and it goes into debug mode. I have typed the macro exactly as you have it. I did change Sheet2 to Sheet1. As a side note, how would I default this to the active sheet? Thanks - Bruce -- VBA Rookie "JMB" wrote: This should find the last cell in Col A that has an entry. Then changes the font in column A-H to bold for that row. Modify to use whatever formatting you wanted. Sub test() With Worksheets("Sheet2") With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub "Calle" wrote: Hi! Is it possible for a macro to format the last row from say A-H in a worksheet. I can't do this manually since the length of the worksheet changes from time to time. Regards Calle |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
JMB;
Below is my copied routine. The With statement is highlighted in the debugger. The spreadsheet has 2 lines of title, 2 blank lines, 1 more title line, a blank line, and then continuous detail, a blank line or two, and then a final line. It is possible to have multiple "breaks " of blank lines throughout the report. Sub XX() ' ' XX Macro ' ' With ActiveSheet With .Cells(.Rows.Count, 1).End(x1up).Resize(1, 8) .Font.Bold = True End With End With Range("A6").Select End Sub -- VBA Rookie "JMB" wrote: To work with the activesheet - you can qualify it Sub test() With ActiveSheet With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub or just use (the post will likely get wrapped funny - there is three lines in the following macro) and VBA will assume the activesheet Sub test() Cells(Rows.Count, 1).End(xlUp).Resize(1, 8).Font.Bold = True End Sub You would have to copy the code from your macro into a post before I can try to figure out why it is not working (and the error description and which line it breaks on would also help) - it runs okay for me. Note there is a period in front of Cells and Font for the macro(s) that have a With statement. "Bruce" wrote: JMB; I have tried this code and I get an error and it goes into debug mode. I have typed the macro exactly as you have it. I did change Sheet2 to Sheet1. As a side note, how would I default this to the active sheet? Thanks - Bruce -- VBA Rookie "JMB" wrote: This should find the last cell in Col A that has an entry. Then changes the font in column A-H to bold for that row. Modify to use whatever formatting you wanted. Sub test() With Worksheets("Sheet2") With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub "Calle" wrote: Hi! Is it possible for a macro to format the last row from say A-H in a worksheet. I can't do this manually since the length of the worksheet changes from time to time. Regards Calle |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
x1up : the 1 s/b an l (lowercase letter L)
"Bruce" wrote: JMB; Below is my copied routine. The With statement is highlighted in the debugger. The spreadsheet has 2 lines of title, 2 blank lines, 1 more title line, a blank line, and then continuous detail, a blank line or two, and then a final line. It is possible to have multiple "breaks " of blank lines throughout the report. Sub XX() ' ' XX Macro ' ' With ActiveSheet With .Cells(.Rows.Count, 1).End(x1up).Resize(1, 8) .Font.Bold = True End With End With Range("A6").Select End Sub -- VBA Rookie "JMB" wrote: To work with the activesheet - you can qualify it Sub test() With ActiveSheet With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub or just use (the post will likely get wrapped funny - there is three lines in the following macro) and VBA will assume the activesheet Sub test() Cells(Rows.Count, 1).End(xlUp).Resize(1, 8).Font.Bold = True End Sub You would have to copy the code from your macro into a post before I can try to figure out why it is not working (and the error description and which line it breaks on would also help) - it runs okay for me. Note there is a period in front of Cells and Font for the macro(s) that have a With statement. "Bruce" wrote: JMB; I have tried this code and I get an error and it goes into debug mode. I have typed the macro exactly as you have it. I did change Sheet2 to Sheet1. As a side note, how would I default this to the active sheet? Thanks - Bruce -- VBA Rookie "JMB" wrote: This should find the last cell in Col A that has an entry. Then changes the font in column A-H to bold for that row. Modify to use whatever formatting you wanted. Sub test() With Worksheets("Sheet2") With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub "Calle" wrote: Hi! Is it possible for a macro to format the last row from say A-H in a worksheet. I can't do this manually since the length of the worksheet changes from time to time. Regards Calle |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
JMB;
You are awsome. Thanks. You saved me hours of trial and error. If I wasn't such an old rookie my eyesight would be better and I could have saved you the last response. A further question, if you don't mind. Let's assume I want to do verify a calculation on the last row and Highlight the cell if there is an error. Example: If "Col B,last row" not = "Col R, last row" then highlight "Col R, last row". How would I code this in VBA? Thanks again for your help. Bruce -- VBA Rookie "JMB" wrote: x1up : the 1 s/b an l (lowercase letter L) "Bruce" wrote: JMB; Below is my copied routine. The With statement is highlighted in the debugger. The spreadsheet has 2 lines of title, 2 blank lines, 1 more title line, a blank line, and then continuous detail, a blank line or two, and then a final line. It is possible to have multiple "breaks " of blank lines throughout the report. Sub XX() ' ' XX Macro ' ' With ActiveSheet With .Cells(.Rows.Count, 1).End(x1up).Resize(1, 8) .Font.Bold = True End With End With Range("A6").Select End Sub -- VBA Rookie "JMB" wrote: To work with the activesheet - you can qualify it Sub test() With ActiveSheet With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub or just use (the post will likely get wrapped funny - there is three lines in the following macro) and VBA will assume the activesheet Sub test() Cells(Rows.Count, 1).End(xlUp).Resize(1, 8).Font.Bold = True End Sub You would have to copy the code from your macro into a post before I can try to figure out why it is not working (and the error description and which line it breaks on would also help) - it runs okay for me. Note there is a period in front of Cells and Font for the macro(s) that have a With statement. "Bruce" wrote: JMB; I have tried this code and I get an error and it goes into debug mode. I have typed the macro exactly as you have it. I did change Sheet2 to Sheet1. As a side note, how would I default this to the active sheet? Thanks - Bruce -- VBA Rookie "JMB" wrote: This should find the last cell in Col A that has an entry. Then changes the font in column A-H to bold for that row. Modify to use whatever formatting you wanted. Sub test() With Worksheets("Sheet2") With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub "Calle" wrote: Hi! Is it possible for a macro to format the last row from say A-H in a worksheet. I can't do this manually since the length of the worksheet changes from time to time. Regards Calle |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
Try this:
Sub test() Dim rngCell1 As Range Dim rngCell2 As Range Set rngCell1 = Cells(Rows.Count, 2).End(xlUp) Set rngCell2 = Cells(Rows.Count, 18).End(xlUp) If rngCell1.Value < rngCell2.Value Then rngCell2.Interior.ColorIndex = 36 Else: rngCell2.Interior.ColorIndex = xlNone End If End Sub "Bruce" wrote: JMB; You are awsome. Thanks. You saved me hours of trial and error. If I wasn't such an old rookie my eyesight would be better and I could have saved you the last response. A further question, if you don't mind. Let's assume I want to do verify a calculation on the last row and Highlight the cell if there is an error. Example: If "Col B,last row" not = "Col R, last row" then highlight "Col R, last row". How would I code this in VBA? Thanks again for your help. Bruce -- VBA Rookie "JMB" wrote: x1up : the 1 s/b an l (lowercase letter L) "Bruce" wrote: JMB; Below is my copied routine. The With statement is highlighted in the debugger. The spreadsheet has 2 lines of title, 2 blank lines, 1 more title line, a blank line, and then continuous detail, a blank line or two, and then a final line. It is possible to have multiple "breaks " of blank lines throughout the report. Sub XX() ' ' XX Macro ' ' With ActiveSheet With .Cells(.Rows.Count, 1).End(x1up).Resize(1, 8) .Font.Bold = True End With End With Range("A6").Select End Sub -- VBA Rookie "JMB" wrote: To work with the activesheet - you can qualify it Sub test() With ActiveSheet With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub or just use (the post will likely get wrapped funny - there is three lines in the following macro) and VBA will assume the activesheet Sub test() Cells(Rows.Count, 1).End(xlUp).Resize(1, 8).Font.Bold = True End Sub You would have to copy the code from your macro into a post before I can try to figure out why it is not working (and the error description and which line it breaks on would also help) - it runs okay for me. Note there is a period in front of Cells and Font for the macro(s) that have a With statement. "Bruce" wrote: JMB; I have tried this code and I get an error and it goes into debug mode. I have typed the macro exactly as you have it. I did change Sheet2 to Sheet1. As a side note, how would I default this to the active sheet? Thanks - Bruce -- VBA Rookie "JMB" wrote: This should find the last cell in Col A that has an entry. Then changes the font in column A-H to bold for that row. Modify to use whatever formatting you wanted. Sub test() With Worksheets("Sheet2") With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub "Calle" wrote: Hi! Is it possible for a macro to format the last row from say A-H in a worksheet. I can't do this manually since the length of the worksheet changes from time to time. Regards Calle |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
JMB;
Thanks. I would have never figured this out. Bruce -- VBA Rookie "JMB" wrote: Try this: Sub test() Dim rngCell1 As Range Dim rngCell2 As Range Set rngCell1 = Cells(Rows.Count, 2).End(xlUp) Set rngCell2 = Cells(Rows.Count, 18).End(xlUp) If rngCell1.Value < rngCell2.Value Then rngCell2.Interior.ColorIndex = 36 Else: rngCell2.Interior.ColorIndex = xlNone End If End Sub "Bruce" wrote: JMB; You are awsome. Thanks. You saved me hours of trial and error. If I wasn't such an old rookie my eyesight would be better and I could have saved you the last response. A further question, if you don't mind. Let's assume I want to do verify a calculation on the last row and Highlight the cell if there is an error. Example: If "Col B,last row" not = "Col R, last row" then highlight "Col R, last row". How would I code this in VBA? Thanks again for your help. Bruce -- VBA Rookie "JMB" wrote: x1up : the 1 s/b an l (lowercase letter L) "Bruce" wrote: JMB; Below is my copied routine. The With statement is highlighted in the debugger. The spreadsheet has 2 lines of title, 2 blank lines, 1 more title line, a blank line, and then continuous detail, a blank line or two, and then a final line. It is possible to have multiple "breaks " of blank lines throughout the report. Sub XX() ' ' XX Macro ' ' With ActiveSheet With .Cells(.Rows.Count, 1).End(x1up).Resize(1, 8) .Font.Bold = True End With End With Range("A6").Select End Sub -- VBA Rookie "JMB" wrote: To work with the activesheet - you can qualify it Sub test() With ActiveSheet With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub or just use (the post will likely get wrapped funny - there is three lines in the following macro) and VBA will assume the activesheet Sub test() Cells(Rows.Count, 1).End(xlUp).Resize(1, 8).Font.Bold = True End Sub You would have to copy the code from your macro into a post before I can try to figure out why it is not working (and the error description and which line it breaks on would also help) - it runs okay for me. Note there is a period in front of Cells and Font for the macro(s) that have a With statement. "Bruce" wrote: JMB; I have tried this code and I get an error and it goes into debug mode. I have typed the macro exactly as you have it. I did change Sheet2 to Sheet1. As a side note, how would I default this to the active sheet? Thanks - Bruce -- VBA Rookie "JMB" wrote: This should find the last cell in Col A that has an entry. Then changes the font in column A-H to bold for that row. Modify to use whatever formatting you wanted. Sub test() With Worksheets("Sheet2") With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub "Calle" wrote: Hi! Is it possible for a macro to format the last row from say A-H in a worksheet. I can't do this manually since the length of the worksheet changes from time to time. Regards Calle |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
format last row on sheet with VBA macro
Most welcome.
"Bruce" wrote: JMB; Thanks. I would have never figured this out. Bruce -- VBA Rookie "JMB" wrote: Try this: Sub test() Dim rngCell1 As Range Dim rngCell2 As Range Set rngCell1 = Cells(Rows.Count, 2).End(xlUp) Set rngCell2 = Cells(Rows.Count, 18).End(xlUp) If rngCell1.Value < rngCell2.Value Then rngCell2.Interior.ColorIndex = 36 Else: rngCell2.Interior.ColorIndex = xlNone End If End Sub "Bruce" wrote: JMB; You are awsome. Thanks. You saved me hours of trial and error. If I wasn't such an old rookie my eyesight would be better and I could have saved you the last response. A further question, if you don't mind. Let's assume I want to do verify a calculation on the last row and Highlight the cell if there is an error. Example: If "Col B,last row" not = "Col R, last row" then highlight "Col R, last row". How would I code this in VBA? Thanks again for your help. Bruce -- VBA Rookie "JMB" wrote: x1up : the 1 s/b an l (lowercase letter L) "Bruce" wrote: JMB; Below is my copied routine. The With statement is highlighted in the debugger. The spreadsheet has 2 lines of title, 2 blank lines, 1 more title line, a blank line, and then continuous detail, a blank line or two, and then a final line. It is possible to have multiple "breaks " of blank lines throughout the report. Sub XX() ' ' XX Macro ' ' With ActiveSheet With .Cells(.Rows.Count, 1).End(x1up).Resize(1, 8) .Font.Bold = True End With End With Range("A6").Select End Sub -- VBA Rookie "JMB" wrote: To work with the activesheet - you can qualify it Sub test() With ActiveSheet With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub or just use (the post will likely get wrapped funny - there is three lines in the following macro) and VBA will assume the activesheet Sub test() Cells(Rows.Count, 1).End(xlUp).Resize(1, 8).Font.Bold = True End Sub You would have to copy the code from your macro into a post before I can try to figure out why it is not working (and the error description and which line it breaks on would also help) - it runs okay for me. Note there is a period in front of Cells and Font for the macro(s) that have a With statement. "Bruce" wrote: JMB; I have tried this code and I get an error and it goes into debug mode. I have typed the macro exactly as you have it. I did change Sheet2 to Sheet1. As a side note, how would I default this to the active sheet? Thanks - Bruce -- VBA Rookie "JMB" wrote: This should find the last cell in Col A that has an entry. Then changes the font in column A-H to bold for that row. Modify to use whatever formatting you wanted. Sub test() With Worksheets("Sheet2") With .Cells(.Rows.Count, 1).End(xlUp).Resize(1, 8) .Font.Bold = True End With End With End Sub "Calle" wrote: Hi! Is it possible for a macro to format the last row from say A-H in a worksheet. I can't do this manually since the length of the worksheet changes from time to time. Regards Calle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to format a sheet | Excel Programming | |||
How do I format a sheet using a copy macro? | Excel Programming | |||
can I clean old excel data format with macro on funny spread sheet | Excel Programming | |||
How do I copy a print format from sheet to sheet in excel ? | Excel Discussion (Misc queries) | |||
Can a macro format a hidden sheet? | Excel Discussion (Misc queries) |