iterate columns to select certain cells
 

All,

I have the following to select a range of cells in a column from A2 thru the
last row in the sheet.

Range("A2:A" &
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row).Select

How can I select the very same set of cells but iterate to each of the
columns in turn. I can't figure out how to go to column B,C,D and on. I've
got

Columns.Next.Select

but can't select the cells that were selected in the previous. All it
selects is the first cell.

Help

Dale Jones

iterate columns to select certain cells
 

Hei Dale

Range("A2:A" &
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row).Select
Range("B2:B" &
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row).Select
Range("c2:c" &
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row).Select

regards Yngve

iterate columns to select certain cells
 

Thanx for the reply Yngve.

The problem is, as I should have stated, is I don't know waht the final
number of columns will be. That will not be static.

Any ideas?

Dale


"Yngve" wrote in message
oups.com...
Hei Dale

Range("A2:A" &
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row).Select
Range("B2:B" &
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row).Select
Range("c2:c" &
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row).Select

regards Yngve

iterate columns to select certain cells
 

I'm not sure...

dim LastRow as long
dim LastCol as long
dim iRow as long
dim iCol as long

with activesheet
lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
lastcol = .Cells.SpecialCells(xlCellTypeLastCell).Column

for icol = 1 to lastcol
for irow = 1 to lastrow
msgbox .cells(irow,icol).value
next irow
next icol
end with




Dale wrote:

All,

I have the following to select a range of cells in a column from A2 thru the
last row in the sheet.

Range("A2:A" &
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row).Select

How can I select the very same set of cells but iterate to each of the
columns in turn. I can't figure out how to go to column B,C,D and on. I've
got

Columns.Next.Select

but can't select the cells that were selected in the previous. All it
selects is the first cell.

Help

Dale Jones

--

Dave Peterson

iterate columns to select certain cells
 

hei Dale

try this

Sub DynColNr()
Dim Lc As Long
Dim j As Long
Dim kol As String
' count columns (and change "Ark1" to your sheet name)
Lc = Sheets("Ark1").Range("IV1").End(xlToLeft).Column

For j = 1 To Lc
kol = ColumnLtr(j)
Range(kol & "2:" & kol &
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row).Select
' do things here.
Next j
'MsgBox kol
End Sub

Public Function ColumnLtr(ByVal Column_Number As Long) As String


'Converts a number to a Column Letter
'Leith Ross


Dim Ltr As String
Dim N1 As Long
Dim N2 As Long

'Column must greater than 0
Column_Number = Column_Number - 1
If Column_Number < 0 Then
ColumnLtr = ""
Exit Function
End If


'Maximum Column value is 256
If Column_Number 256 Then Column_Number = Column_Number Mod 256


'Convert to Column_Number Base 26
N1 = Column_Number \ 26
N2 = Column_Number Mod 26


'Convert number to Alpha characters
If N1 = 0 Then
Ltr = ""
Else
Ltr = Chr$(64 + N1)
End If


Ltr = Ltr & Chr$(65 + N2)


ColumnLtr = Ltr


End Function

regards Yngve