simple macro help

[ultratch47]

i have a list of data in one column (maybe 900 rows long)
i would like for this macro (using XL macro recorder) to give me the row # of the last row in this list

IE.
list is C2:C956
macro will place "956" in cell C1 (this number needs to be a regularly written number as it will be used in a formula later on)

thanks for all the help in advance
Tek

 

[jimboy]

Right mouse over the sheet name (i.e. Sheet1) and select view code, then paste this in;

Private Sub Worksheet_Change(ByVal Target As Range)
Range("C1").Value = Range("C65536").End(xlUp).Row
End Sub

Make an amendment to you sheet (i.e. change a cell) and it will run...

 

[Blue Dragon]

How about:

Sub NumRows
With Application.ActiveSheet
Range("C1") = .Range("C65535").End(xlUp).Row
End With
End Sub

 

[ultratch47]

Thanks guys
i can't write code, so i hvae another question

do i need the Sub and End Sub commands if this is being used WITHIN another macro

 

[jimboy]

You only need one Sub/End Sub per macro.

if you want the number in another macro use this

Dim myrow as long
myrow = range("C65536").end(xlup).row

 

[ultratch47]

ok another question

i have a formula for counting the blanks within the above list:
IE. =CountBlank(C2:C???)
how do i tell excel what the last reference is (inside the formula while inside a macro)

i am so confused

 

[jimboy]

This should do it;

Dim myrow As Long
myrow = Range("C65536").End(xlUp).Row
Range("E1").FormulaR1C1 = "=COUNTBLANK(R2C3:R" & myrow & "C3)"

 

[ultratch47]

ok
this macro will require the need seperate two sets of data
IE. C2:C900 is one set
BLANK in between
C902:C1500 is another set
i need to just simply delete the second set with a macro line (i will create another macro for the other set later)

so macro will need to identify the FIRST blank in this row (since that blank seperating the sets is the only blank in this column) and delete the entire row below that one cell...

thanks

 

[jimboy]

This will delete the first row with a blank cell in column C (from C2 downwards)

Range("C2").End(xlDown).Offset(1, 0).EntireRow.Delete

 

[ultratch47]

jimboy,

the macro then needs to delete ALL rows from that blank and downward

if blank is C901

delete C901:C65536

thanks for the continued assistance