score:2

*Accepted answer*

yes, although i suspect not quite in the way you're thinking. use an array instead of separate variables `rowa`

, `rowb`

and `rowc`

, so you'd replace

```
dim rowa as long, rowb as long, rowc as long
rowa = 1
rowb = 3
rowc = 7 'example figures
```

with -

```
dim row(1 to 3) as long
row(1) = 1
row(2) = 3
row(3) = 7
```

then your above code can be shortened to the following loop:

```
for a = lbound(row) to ubound(row)
.range("r" & row(a)).formular1c1 = "=rc[-1]"
.range("w" & row(a)).formular1c1 = "=(rc[-5]*rc[-7])+rc[-2]"
.range("y" & row(a)).formular1c1 = "=rc[-12]*rc[-2]"
next
```

score:1

maybe something like this:

```
sub test()
dim letters, rows, i, letter, row
letters = array("r", "w", "y")
rows = array(1, 5, 17) 'rowa, rowb, and so on...
i = 0
j = 0
for each row in rows
for each letter in letters
debug.print letters(i) & rows(j)
' .range(letters(i) & rows(i)).formular1c1 = "=rc[-1]"
' .range(letters(i) & rows(i)).formular1c1 = "=(rc[-5]*rc[-7])+rc[-2]"
' .range(letters(i) & rows(i)).formular1c1 = "=rc[-12]*rc[-2]"
i = i + 1
next letter
j = j + 1
i = 0
next row
end sub
```

score:1

## using 'helper' procedures

```
option explicit
sub usingrefrows()
const rowa as long = 1
const rowb as long = 3
const rowc as long = 5
dim ws as worksheet: set ws = sheet1
dim rrg as range: set rrg = refrows(ws, rowa, rowb, rowc)
intersect(rrg, ws.columns("r")).formular1c1 = "=rc[-1]"
intersect(rrg, ws.columns("w")).formular1c1 = "=rc[-5]*rc[-7]+rc[-2]"
intersect(rrg, ws.columns("y")).formular1c1 = "=rc[-12]*rc[-2]"
end sub
sub usingrefrowsandwriterowsr1c1()
const rowa as long = 1
const rowb as long = 3
const rowc as long = 5
const columnslist as string = "r,w,y"
const formulaslist as string _
= "=rc[-1]" & "," _
& "=rc[-5]*rc[-7]+rc[-2]" & "," _
& "=rc[-12]*rc[-2]"
dim ws as worksheet: set ws = sheet1
dim rrg as range: set rrg = refrows(ws, rowa, rowb, rowc)
writerowsr1c1 rrg, columnslist, formulaslist
end sub
function refrows( _
byval ws as worksheet, _
paramarray datarows() as variant) _
as range
dim rg as range
dim n as long
for n = 0 to ubound(datarows)
if rg is nothing then
set rg = ws.rows(datarows(n))
else
set rg = union(rg, ws.rows(datarows(n)))
end if
next n
if not rg is nothing then
set refrows = rg
end if
end function
sub writerowsr1c1( _
byval rowsrange as range, _
byval columnslist as string, _
byval formulaslist as string)
dim cols() as string: cols = split(columnslist, ",")
dim formulas() as string: formulas = split(formulaslist, ",")
dim ws as worksheet: set ws = rowsrange.worksheet
dim n as long
for n = 0 to ubound(cols)
intersect(rowsrange, ws.columns(cols(n))).formular1c1 = formulas(n)
next n
end sub
```

score:2

i would store the rows in an array, then use `union`

to collect all the rows in a variable and `intersect`

that with each column.

this way you can access all defined rows of a specific column at once.

```
option explicit
public sub example()
dim rowarr() as variant
rowarr = array(1, 3, 17) 'define your rows here
dim allrows as range
with activesheet
dim row as variant
for each row in rowarr
if allrows is nothing then
set allrows = .rows(row)
else
set allrows = union(allrows, .rows(row))
end if
next row
'write in all rows of a specific column
intersect(.columns("r"), allrows).formular1c1 = "=rc[-1]"
intersect(.columns("w"), allrows).formular1c1 = "=(rc[-5]*rc[-7])+rc[-2]"
intersect(.columns("y"), allrows).formular1c1 = "=rc[-12]*rc[-2]"
end with
end sub
```

instead of the loop you can also write:

```
set allrows = .range("1:1,3:3,17:17")
```

like

```
option explicit
public sub example()
with activesheet
dim allrows as range
set allrows = .range("1:1,3:3,17:17")
'write in all rows of a specific column
intersect(.columns("r"), allrows).formular1c1 = "=rc[-1]"
intersect(.columns("w"), allrows).formular1c1 = "=(rc[-5]*rc[-7])+rc[-2]"
intersect(.columns("y"), allrows).formular1c1 = "=rc[-12]*rc[-2]"
end with
end sub
```

but this works only for a smaller amount of rows. if you have more you need to use `union`

Source: stackoverflow.com

#### Related Query

- How do I loop across specific ranges with the row number as the variable?
- How to loop through multiple columns. If cells exceed specific height then add a row and merge text in the above cell with empty cell below
- How do I make a macro to look up a number in a cell, copy specific values, then paste values into a row with the corresponding number?
- How to insert a row along with the format of above row by double clicking on specific cell?
- How to Count the Number of a Specific Character in a Cell with Excel VBA
- How can I count the number of specific shapes with different color in excel
- How to identify the row ranges that contribute into a colour formatted cell which is a subtotal, and applying to across multiple columns?
- How to get the row number of the cell in a for each loop
- How to Find the Length of a Column in VBA with a Variable Last Row
- how to count the number of rows with data from a specific cell in excel vba
- How do I find a value in a row and return the column number with VBA?
- How to loop through a table with a variable string as the table name?
- VBA loop through column to compare each cell with variable and delete the row if it doesn't match
- How can I loop through columns with specific cell ranges and set data validation in VBA?
- How to copy a column from a specific row all the way down to the last row with values?
- How do I loop through cells in a specific column and delete the entire row based on its contents?
- How to use Do Until loop in VBA to select first row with the current date
- How do I loop through a row of data, pull value from specific cell in that row, find it in another row, then enter a value in the intersecting box
- Excel Macro: Print the value of a specific row with variable columns
- How to take the average from one worksheet with variable number of rows and get it onto another?
- How to compare columns that have variable text in Excel across all variables number of sheets then sum the matchs?
- How to compare columns that have variable text in Excel across all variables number of sheets then sum the matchs?
- How to delete entire row if the cell value of a cell does not begin with a number
- How can I replace values in one Excel column with values concatenated from a variable number of columns in the same row?
- How to find and use the row number of a specific value using an Excel macro
- I have a number for the last row of the excel sheet. How do I incorporate this with the range function so that I can autofill some data?
- how to copy text from column of active sheet and past it to another sheet according to the specific row and column number which are in active sheet
- How can I count and log the number of rows in a sheet with a specific month/year value
- How to count the number of rows in excel with data?
- How to return the number of dimensions of a (Variant) variable passed to it in VBA

#### More Query from same tag

- how to add a textbox to a powerpoint presentation using vba
- In Memory, Stand-Alone, Disconnected ADO Recordset
- How to send a highlighted cell value to another cell in another column?
- Cells reference not working in Excel VBA 2010 Function
- VBA Change Pivot Table Field: Error 1004
- What request headers are required for a POST containing XML?
- Simplify the following vlookup
- I am constantly getting 'Run time error 13' with 'mydate = Sheets("Summary").Cells(i, "A").Value'
- Unprotect sheet/Workbook
- Column xdf transpose
- Web scraping in VBA and Excel
- How to pass & convert a VARIANT variable from one sub to an OBJECT variable in another sub?
- Vlookup is not working correctly
- How to specify PathToSubformControl for BrowseTo in access 2010
- Difficulty with the worksheetfunction.match property in VBA
- VBA to loop through text delimited files in a folder and export as csv
- Word 2010 Macros running on my computer but nobody else's
- Combination of If Then and Do Loop (I think)
- Fill In cell colors for Worksheet Function (based on select case & range)
- onLoad ribbon callback fails (not firing/working) from Excel VBA .xlam
- "Run-time error 92: For loop not initialized" Error after the first successful run
- Excel VBA application event triggered twice
- Solidworks macro implementation not working with Python
- vba loop through column and if certain value then increment
- Import XML Data and Split into multiples sheets using Excel VBA
- Dynamic chart title from userform
- How to indent bullet ppt vba
- VBA - Excel: Mention a Range equal to one given with less one row
- DoCmd.OutputTo Method: Excel format that opens for Access 2003 and 2010
- How to apply Autofill function for a dynamic range in VBA?