| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Rightmost Column
Is there an equivalent to
'LastRow = Cells(Rows.Count, "A").End(xlUp).Row' for finding the last column to the right that contains data? -- Bill @ UAMS |
|
#2
|
|||
|
|||
|
Use the xlToLeft constant with End. E.g.,
Dim LastCol As Integer LastCol = Cells(1, Columns.Count).End(xlToLeft).Column Debug.Print LastCol -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "BillCPA" <Bill @ UAMS wrote in message ... Is there an equivalent to 'LastRow = Cells(Rows.Count, "A").End(xlUp).Row' for finding the last column to the right that contains data? -- Bill @ UAMS |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rightmost Column
Hi Chip,
Do you know if you can use this column count to also get the equivalent column letter? For example I get a column count of 54 and that represents column "BB". Is there a way to convert the column count to the string value? Thank you. "Chip Pearson" wrote: Use the xlToLeft constant with End. E.g., Dim LastCol As Integer LastCol = Cells(1, Columns.Count).End(xlToLeft).Column Debug.Print LastCol -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "BillCPA" <Bill @ UAMS wrote in message ... Is there an equivalent to 'LastRow = Cells(Rows.Count, "A").End(xlUp).Row' for finding the last column to the right that contains data? -- Bill @ UAMS |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rightmost Column
I'm not Chip, but you can use something like:
dim myCol as long myCol = 54 dim myColLtr myColLtr = activesheet.cells(1,mycol).address(0,0) msgbox mycolltr 'you'll see BB1 here 'so strip the last 1 from that string: msgbox left(mycolltr,len(mycolltr)-1) But depending on what you're doing, you may not need to know the letter for that column. If you use Cells(), you can use a string or a number for that column parm. HF wrote: Hi Chip, Do you know if you can use this column count to also get the equivalent column letter? For example I get a column count of 54 and that represents column "BB". Is there a way to convert the column count to the string value? Thank you. "Chip Pearson" wrote: Use the xlToLeft constant with End. E.g., Dim LastCol As Integer LastCol = Cells(1, Columns.Count).End(xlToLeft).Column Debug.Print LastCol -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "BillCPA" <Bill @ UAMS wrote in message ... Is there an equivalent to 'LastRow = Cells(Rows.Count, "A").End(xlUp).Row' for finding the last column to the right that contains data? -- Bill @ UAMS -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rightmost Column
Thanks Dave. I found a more efficient way of doing what I want that avoids
having to find the column's letter. But I'll keep your code in mind, thanks. "Dave Peterson" wrote: I'm not Chip, but you can use something like: dim myCol as long myCol = 54 dim myColLtr myColLtr = activesheet.cells(1,mycol).address(0,0) msgbox mycolltr 'you'll see BB1 here 'so strip the last 1 from that string: msgbox left(mycolltr,len(mycolltr)-1) But depending on what you're doing, you may not need to know the letter for that column. If you use Cells(), you can use a string or a number for that column parm. HF wrote: Hi Chip, Do you know if you can use this column count to also get the equivalent column letter? For example I get a column count of 54 and that represents column "BB". Is there a way to convert the column count to the string value? Thank you. "Chip Pearson" wrote: Use the xlToLeft constant with End. E.g., Dim LastCol As Integer LastCol = Cells(1, Columns.Count).End(xlToLeft).Column Debug.Print LastCol -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "BillCPA" <Bill @ UAMS wrote in message ... Is there an equivalent to 'LastRow = Cells(Rows.Count, "A").End(xlUp).Row' for finding the last column to the right that contains data? -- Bill @ UAMS -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rightmost Column
Change this line:
dim myColLtr to dim myColLtr as String Dave Peterson wrote: I'm not Chip, but you can use something like: dim myCol as long myCol = 54 dim myColLtr myColLtr = activesheet.cells(1,mycol).address(0,0) msgbox mycolltr 'you'll see BB1 here 'so strip the last 1 from that string: msgbox left(mycolltr,len(mycolltr)-1) But depending on what you're doing, you may not need to know the letter for that column. If you use Cells(), you can use a string or a number for that column parm. HF wrote: Hi Chip, Do you know if you can use this column count to also get the equivalent column letter? For example I get a column count of 54 and that represents column "BB". Is there a way to convert the column count to the string value? Thank you. "Chip Pearson" wrote: Use the xlToLeft constant with End. E.g., Dim LastCol As Integer LastCol = Cells(1, Columns.Count).End(xlToLeft).Column Debug.Print LastCol -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "BillCPA" <Bill @ UAMS wrote in message ... Is there an equivalent to 'LastRow = Cells(Rows.Count, "A").End(xlUp).Row' for finding the last column to the right that contains data? -- Bill @ UAMS -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I sort by row instead of by column? | Excel Worksheet Functions | |||
| How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
| Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
| up to 7 functions? | Excel Worksheet Functions | |||
| How do I reference every "n" cell in a column in Excel? | Excel Worksheet Functions | |||
Linear Mode
