Need help for macro to Shift Select

[pandatastic]

Hi,

Here is my current macro

Range("b1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.Offset(0, -1)).Select

What I need it to do is to select one less cell counting from the right. Becaseu ctrl+shift selects until the filled cell, but I only need to select until the empty cell.

Any other suggestions are more than welcomed!


Thank you!

 

[Joe4]

Welcome to the Board!

I am not quite sure it is totally clear exactly what you are after. Can you describe an actual example for us?
Like if cells B1:E10 were all filled out, which cell do you want to select and why?

 

[pandatastic]

Welcome to the Board!

I am not quite sure it is totally clear exactly what you are after. Can you describe an actual example for us?
Like if cells B1:E10 were all filled out, which cell do you want to select and why?

of course!

So I have formulas in cell C2:H2, A2:B2 are filled, so is I2 and further.
My spreadsheet has 300,000+ rows, and the number of rows change from month to month depending on the adjustments I make.
I would like to fill the formulas of C2:H2 downward until the last row.


Thank you!

 

[Joe4]

Try this:

    Dim lRow As Long
'   User column A to find last row of data
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
'   Autofill columns C-H down to last row
    Range("C2:H2").AutoFill Destination:=Range("C2:H" & lRow), Type:=xlFillDefault

 

[pandatastic]

It works perfectly!

Thank you!

 

[Joe4]

You are welcome.
Glad I could help!