Search Spreadsheet - Results in either listbox or new spreadsheet
 

Im having a little of a situation here. I have a spreadsheet that has
13 columns and alot of rows. I made a form that would sort the data as
needed. so i have 2 cbo's to help with the sort. As of right now i have
it as follows:

For i = 1 To Worksheets("By Store").Rows.Count
If Worksheets("By Store").Cells(i, column).Value = cboRefine
Then
m = m + 1
For z = 1 To 13
Var1 = Cells(i, z).Value
Sheets("Results").Activate
Worksheets("Results").Cells(m, z).Value = Var1
Sheets("By Store").Activate
Next
End If
Next

This way works perfect and how i need it, however when it activates the
pages the screen is a constant flicker due to the constant page switch.
My qustion is that i was wondering if there is a better way to go about
this routine. Is a listbox maybe a better way to go? if so i might need
some assistance with that because i tried that prior and i couldnt
figure out how to make the values go into individual columns. anyway if
anyone has a feedback please let me know. thankyou.

Search Spreadsheet - Results in either listbox or new spreadsheet
 

Don't activate the other sheet - there is no reason to. Also, there is not
reason to loop through all 65536 rows if you don't have data in them all.

For i = 1 To Worksheets("By Store").Cells(Rows.Count,column).End(xlup).row
If Worksheets("By Store").Cells(i, column).Value = cboRefine Then
m = m + 1
For z = 1 To 13
Var1 = Cells(i, z).Value
Worksheets("Results").Cells(m, z).Value = Var1
Next
End If
Next

--
Regards,
Tom Ogilvy


"Brandon Johnson" wrote:

Im having a little of a situation here. I have a spreadsheet that has
13 columns and alot of rows. I made a form that would sort the data as
needed. so i have 2 cbo's to help with the sort. As of right now i have
it as follows:

For i = 1 To Worksheets("By Store").Rows.Count
If Worksheets("By Store").Cells(i, column).Value = cboRefine
Then
m = m + 1
For z = 1 To 13
Var1 = Cells(i, z).Value
Sheets("Results").Activate
Worksheets("Results").Cells(m, z).Value = Var1
Sheets("By Store").Activate
Next
End If
Next

This way works perfect and how i need it, however when it activates the
pages the screen is a constant flicker due to the constant page switch.
My qustion is that i was wondering if there is a better way to go about
this routine. Is a listbox maybe a better way to go? if so i might need
some assistance with that because i tried that prior and i couldnt
figure out how to make the values go into individual columns. anyway if
anyone has a feedback please let me know. thankyou.