Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Column, Select, and Bold
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Column, Select, and Bold
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 "Yellowbird" wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Column, Select, and Bold
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 "Tom Ogilvy" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Column, Select, and Bold
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.Bol d = True ..Range("A" & Rows.Count).End(xlUp).EntireRow.Font.Bold = True End With End Sub -- XL2002 Regards William "Yellowbird" wrote in message om... | 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 | | | | "Tom Ogilvy" wrote in message ... | 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 | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Column, Select, and Bold
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 "William" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Column Select then Data Select then return to cell A1 | Excel Discussion (Misc queries) | |||
If date in column T is +7 then column C is Bold Red | Excel Discussion (Misc queries) | |||
how to select cells that are bold? | Excel Discussion (Misc queries) | |||
Auto BOLD max value in a column | Excel Worksheet Functions | |||
How to find a value then select that column | Excel Programming |