Selecting a particular range including blanks (Excel 2000 SR1)

Topic

New Reply

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

Reply | Quote

Replies

Christa

OK this is what is called thinking outside the box

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

I not sure that I followed your post, but see if this is what you need:

    With ActiveSheet.UsedRange
        .Columns(.Columns.Count).Select
    End With

If not, can clarify what you need and why the above failed? (not what you’ve recorded)

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thanks for the worksheet, it would have been hard to explain what you wanted otherwise. Test this to see if it always works:

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

Reply | Quote

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

Reply | Quote

Sorry, that’s why I had you test it! Try this.

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

Reply | Quote

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

Reply | Quote

Just start the for loop at whatever row the first “real” formula was — 5 or 6 — don’t have the spreadsheet unzipped here.

Reply | Quote

See if this does what you want:

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.Count, 0).End(xlUp).Row
    Worksheets("Sheet1").Range(Cells(lRow, lCol), Cells(lLastRow, lCol)).Select

Reply | Quote