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

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.

MikeD1224 wrote:

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

--

Dave Peterson

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

--
Gary's Student
gsnu200707


"MikeD1224" wrote:

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

Thank you. That was exactly what I was looking for.

Mike

"Gary''s Student" wrote:

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

--
Gary's Student
gsnu200707


"MikeD1224" wrote:

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