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

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.

   Explore Teams Create a free Team
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, 2 months ago Modified 8 years, 2 months ago Viewed 21k 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 148k1818 gold badges208208 silver badges252252 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 to build open source apps in a highly regulated industry
• Community Products Roadmap Update, July 2024
• Featured on Meta
• We spent a sprint addressing your requests — here’s how it went
• Upcoming initiatives on Stack Overflow and across the Stack Exchange network...
• Policy: Generative AI (e.g., ChatGPT) is banned
• The [lib] tag is being burninated
• What makes a homepage useful for logged-in users

Related

0 Change column number in a set range 0 Excel 2011 VBA - Set Range 569 How to convert a column number (e.g. 127) into an Excel column (e.g. AA) 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

• Is it possible to arrange the free n-minoes of orders 2, 3, 4 and 5 into a rectangle?
• Ideal diode in parallel with resistor and voltage source
• Why does an activated soft fork not invalidate the blockchain?
• How do guitarists remember what note each string represents when fretting?
• Joint measurability in quantum mechanics
• Did the BBC censor a non-binary character in Transformers: EarthSpark?
• Why is Uranus colder than Neptune?
• Use of Compile[] to optimize the code to run faster
• Book that I read around 1975, where the main character is a retired space pilot hired to steal an object from a lab called Menlo Park
• Are there examples of triple entendres in English?
• Is it prohibited to consume things that unclean animals produce?
• Phantom points in QGIS do not dissapear
• Is non-temperature related Symmetry Breaking possible?
• Examples of distribution for which first-order condition is not enough for MLE
• Are there conditions for an elliptic Fq-curve to have a quadratic Fq-cover of the line without ramification Fq-points?
• Do Christians believe that Jews and Muslims go to hell?
• Is it possible to complete a Phd on your own?
• Imagining Graham's number in your head collapses your head to a Black hole
• What does '\($*\)' mean in sed regular expression in a makefile?
• Is there a drawback to using Heart's blood rote repeatedly?
• A chess engine in Java: generating white pawn moves - take II
• How soon should you apply and unapply the sustain pedal after markings?
• Does the Ogre-Faced Spider regenerate part of its eyes daily?
• Geometry question about a six-pack of beer

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