Display All Column Headers in MsgBox

[vba_beginner]

Hello,


If you use the sub:


Sub ShowRows()

MsgBox Rows.Address

End Sub


a Message Box will appear with the value $1:$1048576 in Excel 2010. However, if you change the sub from Rows to Columns:


Sub ShowColumns()

MsgBox Columns.Address

End Sub


it returns the same value. Shouldn't it return $1:$16384 or $A:$XFD instead? I can't think of a workaround, and would like to know if anyone has a suggestion.

 

[Debraj]

Hi VBA_Beginner!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).

Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...

[pre]

Sub ShowRows()
Rows.Select
End Sub
If you run the code you will see.. all Rows are selected.. and [code]Rows.Address

is just showing just the address of all the ROWS.. (hey.. did you notice, at the same time.. all Columns are also selected.. so the answer is still same..)

Sub ShowRows()
msgbox Rows.Count
End Sub[/code][/pre]
will give you what you exactly looking for..


Regards,

Deb

 

[vba_beginner]

Hi Deb,


Thank you. I wasn't sure how active this forum was, but given your quick reply, it looks pretty active! I'll read through the three first green sticky topics, as you recommended. It was a bit difficult navigating through the website, but I'll try the archive of posts.


Thank you for addressing my question, too. I was actually happy with the result displayed in my first sub showing the locked Rows from 1 to 1048576. What I'd like to know is how I could reproduce this result to show all Columns from A to XFD, or just the column index 1 to 16384 would be fine, too. Any suggestions you have would be most appreciated!


-vba_beginner

 

[Debraj]

[pre]

Sub showRecord()

MsgBox "If you goes END toward Down of Cell A1 - >" & _
Range("A1").End(xlDown).Address
MsgBox "If you goes END toward Right of Cell A1 - >" & _
Range("A1").End(xlToRight).Address
MsgBox "If you goes END toward Right & Down of Cell A1 - >" & _
Range("A1").End(xlToRight).End(xlDown).Address

End Sub

[/pre]

 

[Marc L]

Hi "vba_beginner" !   You're right, it should but like in previous versions, that's not !


So this is the code for column index :

[pre]

Sub ShowColumns()
MsgBox "1:" & Columns.Count
End Sub

[/pre]

 

[shrivallabha]

The problem with all columns selected (even if it is for only one row) that VBA interprets as one row selected and it is not wrong either!


To work this out, select the very first row in Excel and run the following code in VBA:

[pre]

Sub ShowColumns()
MsgBox Selection.Address(True, True, xlA1)
End Sub
It will show: "$1:$1"


Now select one cell less i.e. upto column XFC and run the same code posted above. It will show: "$A$1:$XFC$1"

So you need to use one of the tricks posted above. Here's one more trick:

Sub ShowColumns()
MsgBox "$A$1:" & Cells(1, Columns.Count).Address
End Sub

[/pre]

 

[vba_beginner]

Thank you everyone - a lot of unique ways to approach the same problem! This site rocks!