Count Rows macro

M

MikeD1224

I am trying to write a macro that will do the following:

Count the number of used rows in my worksheet and assign it a name or
variable.

Then i would like to Select Column K and enter a "1" in K7. From there i
want to do an autofill to the last used from as determined above.

The number of rows changes with each spreadsheet and I have gotten this work
for one, but the next one doesn't quite work because there are more rows.

Any help would be great.

Thanks
 
D

Dave Peterson

Option Explicit
sub TestMe()

Dim LastRow as long

with worksheets("sheet9999")
lastrow = .cells(.rows.count,"A").end(xlup).row
with .range("k1:K" & lastrow)
.formula = "=row()"
.value = .value
end with
end with
End sub

I used column A to find the last used row in the worksheet. And I used
Sheet9999 as the sheet name--change each as required.
 
G

Gary''s Student

If you want autofil by 1 only:

Sub miked()
Set r = ActiveSheet.UsedRange
last = r.Rows.Count + r.Row - 1
For i = 7 To last
Cells(i, "K").Value = 1
Next
End Sub


If you want autofil with ascending numbers:

Sub miked2()
Set r = ActiveSheet.UsedRange
last = r.Rows.Count + r.Row - 1
j = 1
For i = 7 To last
Cells(i, "K").Value = j
j = j + 1
Next
End Sub
 
Top