Use .Range("A1:A10000") to end of records?

[GhostofDoom]

Hello,

how can we use to get the range endof records of the cells without to use this last part of code below




.Range("S8:S100000".Find(What:=Me.ListBox1.Column(17), LookIn:=xlValues, LookAt:=xlWhole))



is there an solution to use it so we don't have to add :S100000 of rows?

because i believe if there are many more records it will not be able to search them all.

Thanks

[mana]

Range("S8:S" & Rows.count).Find


or


Columns("S").Find

[GhostofDoom]

Hello Mana,

i get an error if i use this



Set findvalue2 = Sheet2.Range("S8:S" & Rows.Count).Find(What:=Me.ListBox1.Column(17), LookIn:=xlValues, LookAt:=xlWhole)
MsgBox (findvalue2.Offset(0, 1).Value)


'Run-time error 91
Object variable or with block variable not set'

[SamT]

Try Range("S:S").Offset(8)...

[GhostofDoom]

Hi SamT,

didn't work :(

[Paul_Hossler]

@GhostOfDoom --- what is it EXACTLY are you wanting to accomplish, not how you think you want to do it

[GhostofDoom]

Hi Paul_Hossler,


Well i like to .find all the rows without to use a default number of rows




Sheet2.Range("S8:S100000")'S100000



like




Set findvalue2 = Sheet2.Range("S8:S").Find(What:=Me.ListBox1.Column(17), LookIn:=xlValues, LookAt:=xlWhole)



so when i search for something and i use the offset it will show me the correct record




MsgBox (findvalue2.Offset(0, 0).Value) ' display the record i looking for from the columns A till AI i use the 0,0 and search the correct cell

[Paul_Hossler]

Well, .Find will only return the first occurrence.

Have you looked at .Filter?

[GhostofDoom]

filter?

what do you mean Paul_Hossler with filter

[Paul_Hossler]

put together and attach a sample workbook with the macro

don't need the userform, just the value from the combobox and in another sheet maybe the results

[SamT]

.Find did not find anything
Or
Listbox column 17 of that list row is empty (Null)



.Range("S8:S" & Rows.Count is perfect.

ListBox1.Column(17) I have a doubt about

[GhostofDoom]

Hello SamT,

just to let you know and the others that helpme out
that i have solved it

indeed it worked with the S"Rows



Dim Lastrow As Integer
Lastrow = Sheet2.Cells(Rows.Count, "S").End(xlUp).Row
Set findvalue = Sheet2.Range("S9:S" & Lastrow).Find(What:=Me.ListBox1.Column(17), LookIn:=xlValues, LookAt:=xlWhole)



Thank you all very much :thumb