Find Last Column, Select, and Bold

Y

Yellowbird

I don't know much about VBA - I usually search for snippets of code
that do what I want and then modify them as necessary. This seems to
work alright for my needs, but then I get stuck on something that
seems like it should be so obvious.

Here's (hopefully) an easy one:

I have a variable number of rows and columns in my spreadsheet. I need
to be able to search for the last column with data in it, select that
column, and then make all text bold, and the same for the last row
with data in it.

I'm able to do this successfully for my last row with:

ActiveSheet.Cells(ActiveSheet.Cells(Rows.Count,
"A").End(xlUp).Row, 1).Activate
Selection.EntireRow.Font.Bold = True

However, when I change "row" to "column" and "xlUp" to "xlToLeft", I
get an error 424.

ActiveSheet.Cells(ActiveSheet.Cells(Column.Count,
"A").End(xlToLeft).Column, 1).Activate
Selection.EntireColumn.Font.Bold = True

I'm sure I'm missing something fairly obvious, but would appreciate
any pointers.

TIA,
Yellowbird
 
W

William

Hi Yellowbird

Sub MakeBold()
With ActiveSheet
..Cells.Font.Bold = False
..Range("IV1").End(xlToLeft).EntireColumn.Font.Bold = True
..Range("A" & Rows.Count).End(xlUp).EntireRow.Font.Bold = True
End With
End Sub

[Assumes Column A and Row 1 contain the last cells that would apply to all
columns and rows]

--
XL2002
Regards

William

[email protected]

| I don't know much about VBA - I usually search for snippets of code
| that do what I want and then modify them as necessary. This seems to
| work alright for my needs, but then I get stuck on something that
| seems like it should be so obvious.
|
| Here's (hopefully) an easy one:
|
| I have a variable number of rows and columns in my spreadsheet. I need
| to be able to search for the last column with data in it, select that
| column, and then make all text bold, and the same for the last row
| with data in it.
|
| I'm able to do this successfully for my last row with:
|
| ActiveSheet.Cells(ActiveSheet.Cells(Rows.Count,
| "A").End(xlUp).Row, 1).Activate
| Selection.EntireRow.Font.Bold = True
|
| However, when I change "row" to "column" and "xlUp" to "xlToLeft", I
| get an error 424.
|
| ActiveSheet.Cells(ActiveSheet.Cells(Column.Count,
| "A").End(xlToLeft).Column, 1).Activate
| Selection.EntireColumn.Font.Bold = True
|
| I'm sure I'm missing something fairly obvious, but would appreciate
| any pointers.
|
| TIA,
| Yellowbird
 
T

Tom Ogilvy

William provided a good solution - as to your problem, you replaced
rows.count with columns.count, but it is being supplied as the rows argument
and the column remains "A". So you would need to do this:

ActiveSheet.Cells(1,ActiveSheet.Cells(1,Columns. _
Count).End(xlToLeft).Column).Activate
Selection.EntireColumn.Font.Bold = True
 
Y

Yellowbird

Thanks, William and Tom.

Unfortunately, both of these solutions still bold column A, not the
last column in the worksheet.

Seems like we're on the right track, but need to be able to find the
last column (which is variable - happens to be FO in this worksheet,
EA in the next, etc.) and bold that.

I tried changing the various values, but still can't seem to select
the last column with data.

Any additional suggestions appreciated.

TIA,
Yellowbird
 
W

William

Yellowbird

Tom's post explained why your original code failed whereas my post attempted
to provide a solution to what you were attempting to do. The code I
suggested assumes your first row of data is in row 1. If this is not the
case and your first row is, say, row 3, then you would need to amend the
first line of code. The resultant sub would be....

Sub MakeBold()
With ActiveSheet
..Cells.Font.Bold = False
'Assuming row 3 is the first row of data
..Range("IV3").End(xlToLeft).EntireColumn.Font.Bold = True
..Range("A" & Rows.Count).End(xlUp).EntireRow.Font.Bold = True
End With
End Sub

--
XL2002
Regards

William

[email protected]

| Thanks, William and Tom.
|
| Unfortunately, both of these solutions still bold column A, not the
| last column in the worksheet.
|
| Seems like we're on the right track, but need to be able to find the
| last column (which is variable - happens to be FO in this worksheet,
| EA in the next, etc.) and bold that.
|
| I tried changing the various values, but still can't seem to select
| the last column with data.
|
| Any additional suggestions appreciated.
|
| TIA,
| Yellowbird
|
|
|
| > William provided a good solution - as to your problem, you replaced
| > rows.count with columns.count, but it is being supplied as the rows
argument
| > and the column remains "A". So you would need to do this:
| >
| > ActiveSheet.Cells(1,ActiveSheet.Cells(1,Columns. _
| > Count).End(xlToLeft).Column).Activate
| > Selection.EntireColumn.Font.Bold = True
| >
| > --
| > Regards,
| > Tom Ogilvy
| >
 
Y

Yellowbird

Thanks for the clarification, William.

That did the trick. My data starts in row 2, so once I changed the
"IV" value to "IV2", everything worked as expected.

Yellowbird
 
Top