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

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

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

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

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.

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.

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.

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...

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...

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

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

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

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

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

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

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

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