iterate columns to select certain cells

[Dale]

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

[Yngve]

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

[Dale]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Dave Peterson]

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

[Yngve]

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