selecting a range of columns/rows

[Luis939]

How would I select a set of columns from say D to N but one at a time, first select column D, then perform some instructions, then column E and do the same until you reach column N. Would it be the same for rows? Thank you

 

[Zathras]

This illustrates one possible way:
[blue]

Option Explicit

Sub demo()
Const COL_FIRSTCOL = 4 ' Column "D"
Const COL_LASTCOL = 14 ' Column "N"
Dim rColumn As Range
Dim c As Range
Dim nCol As Integer
Dim nFirstRow As Long
Dim nLastRow As Long

  With ActiveSheet.UsedRange
    nFirstRow = .Row
    nLastRow = .Rows.Count + .Row - 1
  End With
  For nCol = COL_FIRSTCOL To COL_LASTCOL
    Set rColumn = Range(Cells(nFirstRow, nCol), _
                        Cells(nLastRow, nCol))
    For Each c In rColumn
      MsgBox c.Address
    Next c
  Next nCol
  Set rColumn = Nothing
End Sub

[/color]

 

[xlbo]

For i = 4 to 14
columns(i).select
'do stuff
next i

and yes - exactly the same syntax for rows

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Luis939]

oh ok thats great!! thanks for the help!

 

[Luis939]

Just as a follow up, how would i do that for just selected columns, such as
for columns D F and H peform these instructions, thanks

 

[xlbo]

If it is a consistent number of "jumps" ie it always misses a column etc then use the STEP function
For i = 4 to 8 step 2
msgbox i
next i

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Luis939]

well now that I have examined my spreadsheets, i have to perform my instructions with columns D, E, G, H, I, K, L and N, so for not specifying that before, thanks!

 

[Zathras]

Try this variation on the theme:
[blue]

Option Explicit

Sub demo()
Dim rColumn As Range
Dim c As Range
Dim aColumnIDs As Variant
Dim aColumns(7) As Integer
Dim i As Integer
Dim nCol As Integer
Dim nFirstRow As Long
Dim nLastRow As Long

  aColumnIDs = Array("D", "E", "G", "H", "I", "K", "L", "N")
  For i = 0 To 7
    aColumns(i) = Asc(aColumnIDs(i)) - Asc("A") + 1
  Next i
  
  With ActiveSheet.UsedRange
    nFirstRow = .Row
    nLastRow = .Rows.Count + .Row - 1
  End With
  For i = 0 To 7
    nCol = aColumns(i)
    Set rColumn = Range(Cells(nFirstRow, nCol), _
                        Cells(nLastRow, nCol))
    For Each c In rColumn
      MsgBox c.Address
    Next c
  Next i
  Set rColumn = Nothing
End Sub

[/color]

 

[xlbo]

mmmmm column arrays - my favourite method for copying randomly placed data ;-)

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Loomah]

Hi Guys
I just wanted to share something I was told a long time ago by a Mr Tom Ogilvy, xl mvp.

"...It is not recommended to traverse the elements of an array using For...Each..." Apparently there is a KB article on this but I never bothered my lazy bot to find it!

Anyway, to offer an alternative - basically zathras' code adapted....

This has the other advantage that if the columns change you only have to change the array and don't have to go looking for the For Next loops.

This is actually a method I've used in the 'real world' some time ago to pull data from numerous files that weren't uniform in their layout (randomly placed??)to say the least!!

Sub demoToo()
Dim rColumn As Range
Dim c As Range
Dim aColumnIDs As Variant
Dim i As Integer
Dim nFirstRow As Long
Dim nLastRow As Long

  aColumnIDs = Array(4, 5, 7, 8, 9, 11, 12, 14)
  
  With ActiveSheet.UsedRange
    nFirstRow = .Row
    nLastRow = .Rows.Count + .Row - 1
  End With
  For i = LBound(aColumnIDs) To UBound(aColumnIDs)
    Set rColumn = Range(Cells(nFirstRow, aColumnIDs(i)), _
                        Cells(nLastRow, aColumnIDs(i)))
    For Each c In rColumn
      MsgBox c.Address
    Next c
  Next i
  Set rColumn = Nothing
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Luis939]

i have a new question now regarding something very similar to this lets say i have a new formula in one cell, how do i copy the formula to the same columns I was asking about before, but ofcourse the formula is not the same one, so for column C you have cell C50 = C2+C4 and in D50 = D2+D4, basically im just doing a paste special by formulas, thanks!!

 

[Zathras]

Well, you could use something like this:
[blue]

  Range("D50:M50").FormulaR1C1 = Range("C50").FormulaR1C1

[/color]

 

[Luis939]

Beautiful!!!