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

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

Thx! Is there anyway to get it to ignore if tehre is a formula in that cell?

Regards Calle

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

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.

It didn't work. The macro still wont regard the empty cells with only
formulas in them as empty.

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

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

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

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