Ask Experts Questions for FREE Help !
Ask
    murphyt10's Avatar
    murphyt10 Posts: 2, Reputation: 1
    New Member
    		  
    #1

    Aug 12, 2009, 10:45 AM
    Macro - select range based on cell data
    I need a macro that will select a range based on a number in a cell. If the number in the cell (in this case the cell is K1) is 200 then I want to select Range A2:D201, if it was 100 then select Range A2:D101.

    Thanks for the help!
    twinkiedooter's Avatar
    twinkiedooter Posts: 12,172, Reputation: 1054
    Uber Member
    		  
    #2

    Aug 12, 2009, 11:35 AM

    Sounds right to me. Try it and see if it works.
    murphyt10's Avatar
    murphyt10 Posts: 2, Reputation: 1
    New Member
    		  
    #3

    Aug 12, 2009, 11:42 AM

    The answer that was posted seems to have gotten deleted, can you repost please? Thanks!
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #4

    Aug 12, 2009, 01:47 PM

    Following your question, this does what you ask:
    Code:
    Sub SelectRange()
Dim LR As Long
LR = Range("K1").Value + 1

Range("A2:A" & LR).Select

End Sub
    It's possible to have the macro figure out how many cells in column A have data in them, if you wish to automate that, too, so you don't have to put a value in K1:

    Code:
    Sub SelectRange()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

Range("A2:A" & LR).Select

End Sub
    HeidiG's Avatar
    HeidiG Posts: 1, Reputation: 1
    New Member
    		  
    #5

    Aug 3, 2012, 01:53 AM
    That bit of code helped me very much. Thanks :-)
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Macro to save a file name as per a cell content. [ 3 Answers ]

I have a macro saving the file auto. To my desktop. For example cell A1 = 501 I run a macro with a counter and auto. Changes A1 to 502. Now macro must change file saving to 502, or =cell A1 content. I also sometimes have to change destination from desktop to a specific file.

Cell references within VBA in an Excel macro [ 1 Answers ]

In a macro in Excel I want to refer to a cell where the user entered a number. I want that number to be the row number for the macro to move to. (I will then select the entire row and copy it to row 1, which I know how to do). How can I tell the macro which row to go to, using the number...

Macro coping data from many workbooks into one [ 3 Answers ]

Hello, Please could someone help me? I need to write a macro that will open up a folder containing over 3000 workbooks, copy the data from each workbook and paste the data into a new workbook. How can I get the macro to copy the data from the first workbook into a new workbook and then copy...

Cell to trigger a macro [ 2 Answers ]

Hi there I am newbie here and I have a question regarding Excel VBA. Though I done a search, but none of it could meet what I wanted. My request is.. Let say I enter value in A1 & B1. In cell C1, I have a formula, =sum(A1:B1), which is the total. Now, I want to create a event change (VBA)...

Excel: Macro to designate a color based on condition [ 1 Answers ]

I have an Excel which has three column. First has name(which might repeat in the coming cells) ,second has condition and the third has color. I want to count the number of color for a particular condition against each name Thanks Bhaskar KP

View more questions Search