Macro looping?

[klh84]

How do I code a macro so that it loops until it goes through all of the
information on the excel file? This way I do not have to rewrite the same
code for 20 lines one day and then 30 lines the next day.

 

[Don Guillett]

sub loopit()
lastrow=cells(rows.count,"a").end(xlup).row
for i= 1 to lastrow
cells(i,1)=??
next i
end sub

 

[Eduardo]

Hi,
something like this maybe

Dim rng1 As Range

' To start statement to delete rows
Worksheets("Projects").Select

With ThisWorkbook.Sheets("Projects")

With Columns("e")

Do

Set rng1 = .Find(Me.TxtProjectCode.Value, LookIn:=xlValues,
LookAt:=xlWhole, _
MatchCase:=False)

If rng1 Is Nothing Then Exit Do

rng1.EntireRow.Delete


Loop

End With

End With

 

[Chip Pearson]

You must first define what consitutes the "end" of the worksheet. If
you have data in column A and you want to loop through column A until
a blank cell is encountered, use something like

Dim R As Range
Set R = Worksheets("Sheet1").Range("A1")
Do Until R.Value = vbNullString
' do something with R
Set R = R(2, 1)
Loop


Or, if you can define one column whose last non-blank cell indicates
the end of the worksheet, you can use something like

Dim WS As Worksheet
Dim Col As String
Dim LastRow As Long
Dim StartRow As Long
Dim RowNdx As Long

Set WS = Worksheets("Sheet1") '<<<<
Col = "K" '<<<<
StartRow = 1 '<<<<
With WS
LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row
For RowNdx = StartRow To LastRow
.Cells(RowNdx, "A").Value = whatever
Next RowNdx
End With


There are any number of other ways of defining what constitutes the
"end" of a worksheet. You might want to be more specific about what
you really need.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com