beginning programming: stop when last column in sheet

[Guest]

Please help: I would like to change the hard coded 10 in the for loop to a
variable that represents the last column in the sheet.


Sub Macro1()
Worksheets(1).Activate

For i = 1 To 10

Worksheets(1)Columns(i).Select

Selection.TextToColumns , DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(20, 1)),
TrailingMinusNumbers:=True

ActiveCell.Offset(columnOffset:=1).Select

Next i

End Sub

 

[Tom Ogilvy]

Sub Macro1()
Dim lastCol as Long
Dim i as Long
With Worksheets(1)
.Activate
lastCol = .Usedrange.columns( _
.UsedRange.columns.count).column
End with
For i = 1 To lastCol
Worksheets(1).Columns(i).Select
Selection.TextToColumns, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(20, 1)), _
TrailingMinusNumbers:=True
Next i
End Sub

 

[Anne Nasser]

Hi,

How would that apply for a variable that represents the last row of a
dataset in a worksheet?

Also, rather than a hardcoded range such as a1:k22225, is there a
variable which automatically represents the range of all the columns and
rows in a dataset. Could this same variable be applied to all
worksheets whether the dataset is 2 columns and 5 rows or 100 columns
and 50,000 rows?

One more question - how can you specify the range of a column which is
not hardcoded by A,B,C, etc. but identified by a certain label in the
first row (the column heading). For example, if this first row label
were "name" - how could this range be specified?

Anne Nasser

 

[Tom Ogilvy]

How would that apply for a variable that represents the last row of a

dataset in a worksheet?

Dim lastrow as Long, rng as Range
lastRow = 2000
set rng = Range("A1:K" & lastrow)

------------
Dim rng as Range
set rng = Activesheet.Range("A1").CurrentRegion

should define your database. It is equivalent to clicking in A1, then doing
Ctrl+shift+8. This floods out in all directions until it reaches a
completely blank row/column or the edge of the worksheet. This assumes the
data starts in A1. You can anchor from any cell in the database.

-------------

Dim res as Variant, rng as Range
res = Application.Match("name",Rows(1),0)
if not iserror(res) then
set rng = Range("A1").Cells(1,res)
set rng = Range(rng, rng.End(xldown))
' or if there are spaces embedded in the data in that column
' set rng = Range(rng,cells(rows.count,rng.column).End(xlup))
End if

 

[Anne Nasser]

Tom,

Thanks for those. For the first one though regarding stopping at the
last row, if you don`t know the number of the last line, for example,
2000 - what variable could be put to represent this?

Anne

 

[Norman Jones]

Hi Anne,

Tom's response warrants careful consideration; it effectively represents a
mini-tutorial. Certainly, a proper assimilation would enable you to respond
to your own post and more.

Tom already shows you how to define the lastCell range. To return the
corresponding row number only requires the appendage of .Row.

Thus, for column A:

lastRow = Cells(Rows.Count, "A").End(xlUp).Row