CODEWITHSUNDEEP
excelvba
Badja
Badja

Reputation: 875

How to avoid using Selection and Select when going to the bottom of a column

I'm trying to stop using ActiveCell etc as StackOverflow has very much declared this a "nono"

My current code is:

Sub SitesAndProd()

Set wb = ActiveWorkbook
Set ws = Worksheets("Data")
Set rng = ws.Cells(1, 13)

    LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    rng.FormulaR1C1 = "SitesAndProd"                                              'Rename Cell SitesAndProd

Set rng = ws.Cells(2, 13)

    rng.FormulaR1C1 = "=RC[-12]&RC[-4]"
    rng.Offset(0, -1).Select                                                     'Move left 1 column
    Selection.End(xlDown).Select                                                        'Go to bottom of column
    rng.Offset(0, 1).Select                                                      'Move right 1 column
    Range(Selection, Selection.End(xlUp)).Select                                        'Go to top of Column
    Selection.FillDown                                                                  'Copy Formula Down "Fill"

    Selection.Copy                                                                      'Ctrl + C
    Selection.PasteSpecial xlPasteValues                                                'Right click + V
    Application.CutCopyMode = False                                                     'Esc (stops the crawling ants


End Sub

When using Selection.End(xlDown).Select and xlUp later - it's not saving the range position

What's the best way to make sure the range is kept here?

When using the following:

Range("M2").Select
ActiveCell.FormulaR1C1 = "=RC[-12]&RC[-4]"
Range("M2").Select
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown

The code will pull the correct form - going left 1, to the bottom, right 1, selecting up to to the, then copying down

Any chance that someone can point me in the right direction to be able to do this without ActiveCell, Selection and Select?

Upvotes: 0

Views: 113

Answers (2)

jkpieterse
jkpieterse

Reputation: 2986

I expect your code:

Range("M2").Select
ActiveCell.FormulaR1C1 = "=RC[-12]&RC[-4]"
Range("M2").Select
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown

can be replaced with:

Range(Range("M2"), Range("M" & ActiveSheet.Rows.Count).End(xlUp)).Formula = "=RC[-12]&RC[-4]"

If the column which you would like to use to determine the last filled cell is column Q:

Range(Range("M2"), Range("Q" & ActiveSheet.Rows.Count).End(xlUp).Row).Formula = "=RC[-12]&RC[-4]"

Upvotes: -1

Damian
Damian

Reputation: 5174

This is supposing the LastRow you calculated on column A equals the same amount of rows in column M

Option Explicit
Sub SitesAndProd()

    Dim wb As Workbook, ws As Worksheet, LastRow As Long

    Set wb = ThisWorkbook
    Set ws = Worksheets("Data")
    ws.Cells(1, 13) = "SitesAndProd"
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    With ws.Range(ws.Cells(2, 13), ws.Cells(LastRow, 13))
        .FormulaR1C1 = "=RC[-12]&RC[-4]"
        .Value = .Value
    End With

End Sub

I've tweaked some of your code. You need to declare your variables, wb As Workbook and ws As Worksheet. If workbook is the one you got your code in, use ThisWorkbook instead ActiveWorkbook you will get less errors from that.

Edit: Try to avoid as much the global variables. Pass them on your subs or functions as variables.

Upvotes: 2

Related Questions