Automatic Array Selection, depending on how much cells contain DATA

[LucHoffmann]

Hi,

I have totally no expierence with writing macro's and always use the record button. I want to use excel to analyse test data. Here starts the problem.

When I stop the test it always ends with a unknown ammount of row filled with '****'. When I start using functions these cells give errors.

I tried looking these cells up and replacing them, but because of the '****' Excel looks up all cells containg 4 of or more digits.

I can use a formula the determine the last cell containing normal data, but i can't automate the array selection in a way it will chose that cell.

Summarizing my problem:

When cell A5 till A1500 contain numbers and A1501 till A2000 contains '****', I can get the array adress $A$5:$A1500 in a cell.
The row number in which the cell data with '****' starts, can vary. Which is the main problem.

But how can I use this cell as an variable for my Array selection, in a VBA formula or cell formula. Or isn't this possible? Even a solution to replace the '****' by empty cells would be enough, but isn't prefered.

Hope you guys can help me.

 

[SteveO59L]

Something along the lines of

Dim c As Range, rng
Dim LASTROW As Long
Dim row_count As Long


LASTROW = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("a5:a" & LASTROW)
For Each c In rng
If c.Value = "****" Then
row_count = c.Row - 1
MsgBox row_count
Exit Sub
End If
Next c



finds the first **** entry, end sets the prev row to a variable.

this variable can then be used to limit your range selection to your valid numeric entries

 

[LucHoffmann]

Thanks, I will try it!

If someone got other ideas? More then welcome!