Output Range same as input range

Question

I have some history working with VBA, but can't seem to find the solution to this problem. I found an iteration process to select a cell, do a process, and then select the next cell and do the process again, until NULL. I am having a problem outputting each of the processes solutions into the next column. Here is what I have:

Sub Name ()

Dim X As Integer
Dim MyString as String

Application.ScreenUpdating = False
NumRows = Range("D2", Range("D2").End(xlDown)).Rows.Count
Range("D2").Select
For X = 1 To NumRows
    MyString = ActiveCell.Value
    MyString = Right(MyString, Len(MyString)-6)
    Range("I2 to I#").Value = MyString
    ActiveCell.Offset(1,0).Select
Next X

End Sub

Range("I2 to I#").Value = MyString is the line that I need help with. I need it to increment to I3, I4, I5, etc. until it reaches NumRows count.

Please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Tim Stack, May 28 '19 at 07:06
Also avoid `integer` variables, the value you might want to store can be bigger than `integer` can hold. — JvdV, May 28 '19 at 07:11
Use Long @Zman3. And using `Select` and `Activate` is inviting a lot of trouble into your code — Tim Stack, May 28 '19 at 07:13
Adding to @JvdV's comment - when you declare an `Integer` in the newer versions of VBA, it *actually* declares the memory for a `Long`, but locks the rest out (so every `Integer` effectively wastes memory). There is no advantage to ever using `Integer` instead of `Long` - it is mostly only left in for backwards compatibility. Writing new code, always `Dim yourVariable As Long` — Chronocidal, May 28 '19 at 07:13

score 1 · Accepted Answer · answered May 28 '19 at 07:13

1

When working with Cells the best way to loop through them is For Each Cell in Range so taking this and as comments told you to avoid selecting, this should help you:

Option Explicit
Sub Name()

    Dim C As Range, MyRange As Range
    Dim LastRow As Long

    Application.ScreenUpdating = False
    With ThisWorkbook.Sheets("MySheet") 'Change MySheet for your working sheet name
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row 'last row on column D
        Set MyRange = .Range("D2:D" & LastRow) 'declare your working range
        For Each C In MyRange
            If Not C = vbNullString Then .Cells(C.Row, "I") = Right(C, Len(C) - 6)
        Next C
    End With
    Application.ScreenUpdating = True

End Sub

answered May 28 '19 at 07:13

Damian

5,152
1
10
21

Perfect, thank you. This works great! Could you please explain how the "&" works? – Binx May 28 '19 at 07:33
Its for concatenate `"Hello" & "World"` = `HelloWorld`. `"D2:D" & LastRow` = `"D2:D100"`(if `LastRow = 100`). Works on Excel the same way it does on VBA. – Damian May 28 '19 at 07:36
It's worth noting the difference in determining the last row here. OP's method will find the last non-blank cell in a column, while Damian's method will find the true last filled cell in a column, not taking any blank intermediate cells into account – Tim Stack May 28 '19 at 07:40
Might be a silly question, but why couldn't you have "D2:DLastRow"? – Binx May 28 '19 at 07:40
VBA process the variables, for vba `LastRow = 100`, but `"D2:DLastRow"` = `"D2:DLastRow"`. Anything between double quotes means a `String` and vba won't process the variable `LastRow`, instead it will try to process the whole string causing error. – Damian May 28 '19 at 07:42
@TimStack and about your comment, I wanted to set the last row with data because there could be blank cells in between (maybe not, but I don't know that) so this code will loop through the last row and ignore the blank cells in between. – Damian May 28 '19 at 07:45
Yes @Damian, that's what I explained in my comment. Just valuable information for OP, I am sure you know the difference between `End(xlUp)` and `End(xlDown)` – Tim Stack May 28 '19 at 07:48
I wanted to test your claim that a `For each` loop would be for the best, so I compared it against a `For to` loop. Each method did 3 million iterations, spread over 6 separate loops of 500'000 rows. The `For each` method averaged at `2.05 seconds`, the `For to` method at `2.28 seconds`. So, the `For each` method is on average `11.2%` faster, when going through and populating a range of cells. Quite interesting! – Tim Stack May 28 '19 at 08:20

score 0 · Answer 2 · answered May 28 '19 at 08:15

Another solution is Do Until. You could use this method if you dont have empty cells in the middle of your data.

Option Explicit

Sub Test()

    Dim StartingPoint As Long

    StartingPoint = 2 'Set the line to begin

    With ThisWorkbook.Worksheets("Sheet1") 'Set the worksheet

        Do Until .Cells(StartingPoint, "D").Value = "" 'Repeat the process until you find empty cell

           .Cells(StartingPoint, "I").Value = Right(.Cells(StartingPoint, "D").Value, Len(.Cells(StartingPoint, "D").Value) - 6)

           StartingPoint = StartingPoint + 1

        Loop

    End With

End Sub

Output Range same as input range

2 Answers2

Linked