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

Question

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?

https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — DisplayName, Mar 05 '19 at 14:51
Thanks. I've been using this a lot, but ActiveCell.Offset is quite specific and not covered in this - I was hoping for some slightly more specific help — Badja, Mar 05 '19 at 14:53
your each and every use of `ActiveCell` simply derives from its preceeding `.Select`. Hence just avoid `Select` by means of any of those good pieces of advice you can find in the link I provided and you'll "automagically" get rid of any need of `ActiveCell` — DisplayName, Mar 05 '19 at 14:57
I'm avoiding that using `With` currently. But `With rng .Offset...` Requires a `=` or `.` - where .Select is the first thing that comes to mind — Badja, Mar 05 '19 at 14:59
Is there anything you are looking for on this code? Some code word, or value? Any pattern to get a more efficient code? — Damian, Mar 05 '19 at 15:02
It's more a way of getting out of the habit, as I've been doing it too long. Unfortnautley I'm so used to `.Select` at the end of `Offset` that I don't know what comes next. I'm almost there using `With` however — Badja, Mar 05 '19 at 15:04
Can you put me an example of what are you intending with this code? I could give you a different approach. — Damian, Mar 05 '19 at 15:05
`With …` doesn't require any `=`. Take a thorough reading of given link and you'll find what you need. As a rule of thumb you can melt two consecutive codelines, the first ending with `...Select` and the second staring with `ActiveCell...` (or `Selection…`), erasing both `Select` and `ActiveCell` (or `Selection`) — DisplayName, Mar 05 '19 at 15:06
Text example: Put formula in `M2`. Fill down. The best way I can do this is: go left 1, go down, go right 1, go up — Badja, Mar 05 '19 at 15:08
The best way to do this is for example declaring a `LastRow` variable where you store the last cell with text. `LastRow = ws.Cells(ws.rows.count, 1).End(xlUp).Row` this would store the last row on column A which has text. Then you would use the with like: `With ws.Range("M2:M" & LastRow) .FormulaR1C1 = The Formula you need .Value = .Value End With` (ws is a worksheet variable which stores the worksheet you are working on) — Damian, Mar 05 '19 at 15:11
There might be some useful pearls of wisdom here : http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html — Solar Mike, Mar 05 '19 at 15:11
@Damian that worked perfectly. Rather weirdly, I had the same `LastRow` declaration on my `Public` for use elsewhere. If you put your comment as an answer, I'll give it green. — Badja, Mar 05 '19 at 15:19

Damian · Accepted Answer · 2019-03-05T15:41:11.260

2

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.

edited Mar 05 '19 at 15:41

answered Mar 05 '19 at 15:21

Damian

5,152
1
10
21

Is there a reason not to use the variables global, but in each sub? Thank for the answer, you basically have what I have now, minus the global declaration – Badja Mar 05 '19 at 15:25
1

One quick note `LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row` might have unexpected behavior because `Rows.Count` references the active sheet which could be different from the sheet referenced by `ws`. – Nacorid Mar 05 '19 at 15:27
1

Because you could change the global variable in some function or procedure without noticing and it would screw all your work. These errors are harder to find... For example if you got the LastRow from another procedure, just use this `Sub SitesAndProd(LastRow As Long)` and when you need to call that procedure use `Call SitesAndProd(Variable holding ur last row)` – Damian Mar 05 '19 at 15:28
One last bit for you, could you just edit the answer (for others) to have `ws.Cells` please :) – Badja Mar 05 '19 at 15:34
I don't understand... what do you mean? – Damian Mar 05 '19 at 15:36
Sorry, when running it, it says `Invalid or unqualified reference` - but with `ws.Cells` instead of `.Cells` it works fine. This is on the `With ws.Range(.Cells...` line – Badja Mar 05 '19 at 15:40
True that! Sorry I started using just With ws, but the code was gonna get larger and then I decided to use the whole range in the with but totally forgot to add the "ws" – Damian Mar 05 '19 at 15:41
I've never used .Value = .Value - how do I use this but keep leading 0's? The data is a 6 digit and a 3 digit number ,often both with at least 1 leading 0 so I need them in – Badja Mar 05 '19 at 15:44
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/189464/discussion-between-damian-and-badja). – Damian Mar 05 '19 at 15:45

jkpieterse · Answer 2 · 2019-03-06T08:10:50.987

-1

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]"

edited Mar 06 '19 at 08:10

answered Mar 05 '19 at 15:11

jkpieterse

2,727
1
9
18

This just fill `M1:M2` with the formula, it doesn't go to the bottom at all – Badja Mar 05 '19 at 15:14
1

This is precisely why selecting things is so confusing. My code detects the first and last filled cell in column M. Which column should be used to determine the last row to place the formula into? I have added an example where I used column Q – jkpieterse Mar 06 '19 at 08:09

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

2 Answers2