Sidhant Shivram
Badges: 16
Rep:
? You'll earn badges for being active around the site. Rep gems come when your posts are rated by other community members.
#1
Report Thread starter 6 years ago
#1
So, I have written this sub routine code in VBA which basically looks for data on one worksheet, if it finds the said data, it cuts & pastes the whole row onto a new worksheet. So, the problem is - I have more than 10,000 data points so I can't keep pressing run 10,000 lines and I would like the code to run until it cuts & pastes ALL the data points that match my criteria onto the new worksheet. How do you suggest I do this? What additions should I make to my code so that it runs itself till all the relevant data has been transferred to the new worksheet?

EDIT: I have tried adding a while loop but I don't know how exactly should I do it.

Sub Riskcon()Dim LR As Long
Range("A2").EntireRow.Insert Shift:=xlDown
LR = Sheets("Panel Data").Cells(Rows.Count, "C").End(xlUp).Row
LR1 = Sheets("VBA").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("Panel Data").Range("C2:C" & LR)
.AutoFilter
.AutoFilter Field:=1, Criteria1:="2004", _
Operator:=xlOr, Criteria2:="2005"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("VBA").Range("A" & LR1)
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub



Thank you very much!
0
reply
Sidhant Shivram
Badges: 16
Rep:
? You'll earn badges for being active around the site. Rep gems come when your posts are rated by other community members.
#2
Report Thread starter 6 years ago
#2
:bump:
0
reply
davros
  • Study Helper
Badges: 16
Rep:
? You'll earn badges for being active around the site. Rep gems come when your posts are rated by other community members.
#3
Report 6 years ago
#3
(Original post by Sidhant Shivram)
So, I have written this sub routine code in VBA which basically looks for data on one worksheet, if it finds the said data, it cuts & pastes the whole row onto a new worksheet. So, the problem is - I have more than 10,000 data points so I can't keep pressing run 10,000 lines and I would like the code to run until it cuts & pastes ALL the data points that match my criteria onto the new worksheet. How do you suggest I do this? What additions should I make to my code so that it runs itself till all the relevant data has been transferred to the new worksheet?

EDIT: I have tried adding a while loop but I don't know how exactly should I do it.

Sub Riskcon()Dim LR As Long
Range("A2").EntireRow.Insert Shift:=xlDown
LR = Sheets("Panel Data").Cells(Rows.Count, "C").End(xlUp).Row
LR1 = Sheets("VBA").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("Panel Data").Range("C2:C" & LR)
.AutoFilter
.AutoFilter Field:=1, Criteria1:="2004", _
Operator:=xlOr, Criteria2:="2005"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("VBA").Range("A" & LR1)
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub



Thank you very much!
I haven't got a similiar example to hand. but in my experience when you filter a range of data, then select the visible cells and copy/paste. it will copy ALL the rows that match the criteria - not just one!

So why do you think you need to run 10,000 separate exectutions? Or are you talking about setting a different criterion each time, or operating on different source worksheets?

Can you describe in English what your code is trying to achieve, or upload a sample workbook if not too large?

To answer your question literally, you would just put the code in its own subroutine and call that from inside a For Next or Do While loop in another subroutine, but I can't see why that's necessary without further information.
0
reply
Sidhant Shivram
Badges: 16
Rep:
? You'll earn badges for being active around the site. Rep gems come when your posts are rated by other community members.
#4
Report Thread starter 6 years ago
#4
(Original post by davros)
I haven't got a similiar example to hand. but in my experience when you filter a range of data, then select the visible cells and copy/paste. it will copy ALL the rows that match the criteria - not just one!

So why do you think you need to run 10,000 separate exectutions? Or are you talking about setting a different criterion each time, or operating on different source worksheets?

Can you describe in English what your code is trying to achieve, or upload a sample workbook if not too large?

To answer your question literally, you would just put the code in its own subroutine and call that from inside a For Next or Do While loop in another subroutine, but I can't see why that's necessary without further information.
Thank you very much for your response.

Here's a link to a more elaborate explanation of my problem:

http://www.excelforum.com/excel-prog...ml#post4016359
0
reply
davros
  • Study Helper
Badges: 16
Rep:
? You'll earn badges for being active around the site. Rep gems come when your posts are rated by other community members.
#5
Report 6 years ago
#5
(Original post by Sidhant Shivram)
Thank you very much for your response.

Here's a link to a more elaborate explanation of my problem:

http://www.excelforum.com/excel-prog...ml#post4016359
OK, unfortunately that doesn't help because I can't see any of your code or content without setting up a userid for that site!

I think I'll leave you in their capable hands - from my experience you're always going to get better support on a dedicated Office site than you could on TSR. My personal favourite is windowssecrets.com where I've been registered for years
0
reply
davros
  • Study Helper
Badges: 16
Rep:
? You'll earn badges for being active around the site. Rep gems come when your posts are rated by other community members.
#6
Report 6 years ago
#6
(Original post by Sidhant Shivram)
stuff
Update:

I've set up an Excel Forum account to have a look at your spreadsheet and I still can't see why you'd need a loop.

If you want to grab all the records between 2 dates you can do this with a single filter statement - just record a macro with an Autofilter applied, and in the Date column choose a Date Filter which allows you to select a date Between one date And another.

If you look at the recorded macro you can see what code is needed. Once the data are filtered, if you go to the Visible cells and do a Copy and Paste it will transfer all the records that match the criteria.
0
reply
X

Quick Reply

Attached files
Write a reply...
Reply
new posts
Back
to top
Latest
My Feed

See more of what you like on
The Student Room

You can personalise what you see on TSR. Tell us a little about yourself to get started.

Personalise

Would you give consent for uni's to contact your parent/trusted person in a mental health crisis?

Yes - my parent/carer (68)
32.85%
Yes - a trusted person (58)
28.02%
No (54)
26.09%
I'm not sure (27)
13.04%

Watched Threads

View All
Latest
My Feed