Set A Range To An Entire Column With Index Number - Stack Overflow

Sorry, we no longer support your browser Please upgrade to Microsoft Edge, Google Chrome, or Firefox. Learn more about our browser support.

1. 1. Home
   2. Questions
   3. Tags
   4. Users
   5. Companies
   6. Labs
   7. Jobs
   8. Discussions
   9. Collectives

   10. Communities for your favorite technologies. Explore all Collectives

2. Teams

   Ask questions, find answers and collaborate at work with Stack Overflow for Teams.

   Try Teams for free Explore Teams
3. Teams

4. Ask questions, find answers and collaborate at work with Stack Overflow for Teams. Explore Teams

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Get early access and see previews of new features.

Learn more about Labs Set a range to an entire column with index number Ask Question Asked 8 years, 8 months ago Modified 8 years, 8 months ago Viewed 24k times 1

I'm building a function that takes a column number as an input to return the highest value in the column.

I need to work with an entire column with a column index number. The following code works but I need an index number instead of using "B:B" to reference the column.

Set rng = Range("B:B")

When I use the following code, everything seems to break down. It always returns a 0 even though the highest number is 44. Can you see what I am doing wrong? It worked perfectly when I used "B:B" as a range.

Set rng = Range(Columns(2),Columns(2))

It doesn't seem to recognize anything in the range. Any help would be awesome!

• vba
• excel
• excel-formula

Share Improve this question Follow edited Apr 15, 2016 at 18:51 noob231454 asked Apr 15, 2016 at 18:41 noob231454noob231454 2711 gold badge11 silver badge55 bronze badges 4

• 1 Those lines of code work for me. What do you mean when you say "everything seems to break down"? Can you provide more code showing what's not working and if you're getting any errors? – tigeravatar Commented Apr 15, 2016 at 18:43
• 1 use columns(2) only. Set rng = columns(2) – Scott Craner Commented Apr 15, 2016 at 18:44
• Instead of returning the highest number in the column, it returns a 0. – noob231454 Commented Apr 15, 2016 at 18:51
• Why do you expect Set rng = Range() to return 44? It's just setting a range. You can also just do Set rng = Columns(2).EntireColumn – BruceWayne Commented Apr 15, 2016 at 18:57

Add a comment |

2 Answers 2

Sorted by: Reset to default Highest score (default) Trending (recent votes count more) Date modified (newest first) Date created (oldest first) 1

Try this

Sub Sample() Dim Rng As Range '~~> This will let you work with Col B Set Rng = Columns(2) '~~> This will give you the Max in that column MsgBox Application.WorksheetFunction.Max(Rng) End Sub Share Improve this answer Follow answered Apr 15, 2016 at 19:01 Siddharth RoutSiddharth Rout 149k1818 gold badges209209 silver badges256256 bronze badges 1

• This is golden! Simpler than what I was trying to do – noob231454 Commented Apr 15, 2016 at 19:05

Add a comment | -1

This is from a solution I found a while back:

Dim x, y As Int Dim rng As Range x = 1 ' For A y = 4 ' For D Set rng = Columns(Chr(64 + x) & ":" & Chr(64 + y))

Source: http://www.mrexcel.com/forum/excel-questions/114025-visual-basic-applications-select-multiple-columns-using-column-number.html

Share Improve this answer Follow answered Apr 15, 2016 at 18:51 Derek DrewDerek Drew 4455 bronze badges Add a comment |

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.
• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Draft saved Draft discarded

Sign up or log in

Sign up using Google Sign up using Email and Password Submit

Post as a guest

Name Email

Required, but never shown

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged

• vba
• excel
• excel-formula

or ask your own question.

• The Overflow Blog
• How AI apps are like Google Search
• Featured on Meta
• The December 2024 Community Asks Sprint has been moved to March 2025 (and...
• Stack Overflow Jobs is expanding to more countries

Related

0 Change column number in a set range 574 How to convert a column number (e.g. 127) into an Excel column (e.g. AA) 0 Excel 2011 VBA - Set Range 0 Change Number Format for Range 0 How to set a range as the intersection of UsedRange WITHOUT first row + Another Column? 0 How to set a range in vba using specific contiguous columns of structured reference table in excel? 0 Pass an intermediate filtered range to INDEX MATCH 1 Set a variable as column range 1 excel formula range reference using column number

Hot Network Questions

• Enforce SSH Private Key Passphrase Complexity
• Change of Measure in the Heston Model Using Girsanov's Theorem
• Growing plants on mars to increase the oxygen level
• Electrician installed NEMA 14-50R 240V receptacle and circuit without ground wire
• How to Modify 7447 IC Output to Improve 6 and 9 Display on a 7-Segment
• How do I recreate these shooting stars?
• What happened to 1T-SRAM?
• Are their any advanatges of a lower value of a loss function?
• Why are so many problems linear and how would one solve nonlinear problems?
• Why did Herod want to know the time of appearance of the Star of Bethlehem?
• Convexity of probabilistic function
• First instance of the use of immersion in a breathable liquid for high gee flight?
• Is it necessary to report a researcher if you are sure of academic misconduct?
• Manathermy: effects on the ecosystem
• How to copy tables without geometries
• How can bevel one end of cube so that it transitions into a rounded end on the other end?
• Free Kei Friday
• Is this position possible to have been made legally?
• Is this 240V compressor plug wired correctly?
• What's left of wine or vodka after the water and alcohol is boiled off?
• Air launch separation mechanism
• Why did the golden eagle attack Dirk Gently?
• What Battery Powered Part Is This?
• The MC dies a few years after an apocalypse, but wakes up years earlier, just days before it starts. Uses his knowledge to gain skills faster

more hot questions Question feed Subscribe to RSS Question feed

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

lang-vb