Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting a particular range including blanks (Excel 2000 SR1)

    I need to select a range in an Excel macro. I don't think it can be a named range because it's going to change every month (move one column to the right). I have the macro selecting the right column, now I just need it to select the column of numbers down to the total. I can do this by using Range(Selection, Selection.End(xlDown)).Select a whole bunch of times (11 to be exact) ...is there an easier way to do it?

    Thanks in advance,

    Christa

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Christa

    OK this is what is called thinking outside the box <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    How about doing the following:

    Range("A" & ActiveSheet.Rows.Count).End(xlUp).Rows

    If you start all the way from the bottom, you will hit the first cell with a 'something', and that would be your range you need to work with. What is between this row and row 1 may include anything spaces or otherwise.

    HTH

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Again Christa

    Sorry I did not copy the whole reply, Named Ranges will move if you insert or delete rows, so you can start from say today and you know the position of the range today, and you know that tomorrow it will move to the right, so what you would do, is add the offset based on how many days has passed since the range was in its original known position.

    But again, if no activities happen on weekends and holidays well your code will need to adjust for that. There are companies that sell you lists of holiday dates for like 25 years in advance, for many countries if you really want to go that way.

    let me know your thoughts.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    That sounds good except that in order to select the column I need to perform the action on, I select a cell at the top by using the GOTO SPECIAL BLANKS function...In order to use the line you suggested I still need to get the active cell from the top of the column to the bottom. See Macro below

    Finds the last column containing formulas and changes them to values
    Range("OperSummValues").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveCell.Offset(0, -1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False

    Thanks,

    Christa

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    I not sure that I followed your post, but see if this is what you need:<pre> With ActiveSheet.UsedRange
    .Columns(.Columns.Count).Select
    End With</pre>

    If not, can clarify what you need and why the above failed? (not what you've recorded) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Also, be sure to reply to just this post instead of creating a new thread; ie press the reply button on this post. Less confusion for the readers. Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    Sammy,

    Thanks for the reply. I'm not sure what exactly
    With ActiveSheet.UsedRange
    .Columns(.Columns.Count).Select
    is supposed to go in my macro because I'm not clear on exactly what it does...sorry.

    My macro is below.

    Thanks,

    Christa

    Sub OperatingSummaryUpdate()
    ' Finds the last column containing formulas and changes them to values
    Range("OperSummValues").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveCell.Offset(0, -1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    ' Copies formulas from the "Formulas" column to the next blank column
    Range("Formula").Select
    Selection.Copy
    Range("OperSummValues").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveCell.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    End Sub

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    See if this does what you want:

    <pre>Dim lCol As Long, lRow As Long, lLastRow As Long
    lCol = Selection.Column
    lRow = Selection.Row
    lLastRow = Worksheets("Sheet1").Cells(1, lCol).Offset(Worksheets("Sheet1").UsedRange.Rows.C ount, 0).End(xlUp).Row
    Worksheets("Sheet1").Range(Cells(lRow, lCol), Cells(lLastRow, lCol)).Select
    </pre>

    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    We really need an exact description of what you want to do. If the macro doesn't do what you want, then we cannot look at it to see what you want to do. Is the following what you want to do?

    I have a spreadsheet that contains formulas and data on the left and a copy of these values on the right. I need a macro to copy just the values from the last column that contains formulas to the first empty column on the right.

    This is what I think that you want from reading your posts. It's only a few lines, but I would like confirmation that this is really what you want to do so that I don't confuse you again. Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    I've attached a file...it's zipped because I could not make it small enough any other way.

    What I would like to do is take the last used column on the sheet called OperatingRevenueSummary...currently Jan-02...select the range containing all the numbers but not including the total, copy and paste the formula that is currently there as values. The last used column is going to change every month and I need the blanks in the columns for "looks"...presentation. My current macro (called OperatingSummaryUpdate) works because I have done a work around and hidden text in the blank cells so they are not technically blank and the Selection xlDown command works.

    I hope this is clear now

    Thanks,

    Christa
    Attached Files Attached Files

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    Thanks for the worksheet, it would have been hard to explain what you wanted otherwise. Test this to see if it always works:<pre>Sub OperatingSummaryUpdate()
    With Sheets("OperatingRevenueSummary") ' with summary sheet
    ' take the last column
    With .UsedRange.Columns(.UsedRange.Columns.Count) _
    .SpecialCells(xlCellTypeFormulas, 23)
    ' but just the formulas
    .Copy ' and copy them
    ' to the last empty column to the left
    .End(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlValues
    End With
    End With
    Application.CutCopyMode = False
    End Sub
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    Thanks for the try...it kind of works...I'll have to try changing it. I want it to copy the formulas into the same rows that they exist in the formula column...just into a different column. The macro right now copies the formulas but skips all the blanks in the column it copies to...so the number end up in the wrong rows.

    Also, I guess I wasn't clear in what I wanted. In the last used column of the range (eg. Jan-02) there will be formulas. I want these formulas changed to values...so essentially I'm copying from and pasting as values into the same column. Then as the second part of the macro, I want to take the formulas in the column titled 'formulas' and copy then to the first blank column to the left of the formulas column.

    Thanks again for your code...it gives me a starting point that I didn't have before.

    Christa

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    Sorry, that's why I had you test it! Try this.<pre>Sub SecondTry()
    Dim rngFormulas As Range
    Dim intOffset As Integer, i As Integer
    With Sheets("OperatingRevenueSummary")
    Set rngFormulas = .UsedRange.Columns(.UsedRange.Columns.Count)
    End With
    intOffset = rngFormulas.Column - _
    rngFormulas.SpecialCells(xlCellTypeFormulas, 23).End(xlToLeft).Column - 1
    For i = 1 To rngFormulas.Cells.Count
    With rngFormulas.Cells(i)
    If .HasFormula Then _
    .Offset(0, -intOffset).Value = .Value
    End With
    Next i
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  13. #13
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    Thanks again, Sammy...the second macro does leave all the blanks where they should be in the column it copies to...but it copies the formulas in the column Jan-02 and pastes them as values into Feb-02 (the column immediately to the right of Jan-02). What I want the macro to do is to paste the formulas in Jan-02 as values into Jan-02...then copy formulas from a column called formulas into Feb-02.

    I'm just using Jan-02 and Feb-02 as examples. If my current month was September I would want to copy the formulas in Sep-02 and paste them as values into Sep-02 and then copy the formulas from the formula column into Oct-02

    I appreciate your help but understand if you don't have all the time that it takes to get this right. I wish that there was a VBA course that I could take in my area but the only one I can find is a very introductory one and I've already taken it.

    Thanks,

    Christa

  14. #14
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting a particular range including blanks (Excel 2000 SR1)

    Just start the for loop at whatever row the first "real" formula was -- 5 or 6 -- don't have the spreadsheet unzipped here.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •