Solved

I need to select only 500 rows covering any range of data (sampling)

Posted on 2014-09-03
5
124 Views
Last Modified: 2014-09-04
Dear All,

I need to select only 500 rows covering any range of the input sheet  (sampling) as attached here. The rows should be selected equally to cover the data range.

I need the first row and the last row and the rows between should be equally selected and moved the output sheet.

Regards,

Dallag
Convert-Large-files-to-500-GA.xlsx
0
Comment
Question by:dallagmm
5 Comments
 
LVL 19

Expert Comment

by:helpfinder
ID: 40302746
It is definitely possible to use some VBA macro to achieve this, but if you need only this I guess the fastest way will be manual work instead of looking for code and make .xlsb
Just press F5 in your Input sheet and type A1:B500 to select first 500 rows and copy-paste it into Output sheet.
0
 

Author Comment

by:dallagmm
ID: 40302768
I need to select 500 rows to cover the range. For example if I have 1000 rows then I should read the first row and skip the second and read the third and skip the fourth. So at the end I will be able to get 500 rows starting from the first row to the last row.

Regards,

Dallag
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40302839
Hi,

pls try

Sub Macro1()
'
' Macro1 Macro

LinesToBeSelected = 500
cnt = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Count
diff = cnt / LinesToBeSelected
Range("A1").EntireRow.Select
For Idx = 1 + diff To (LinesToBeSelected - 1) * diff Step diff
    rw = Int(Idx)
    Union(Selection, Range(rw & ":" & rw)).Select
Next
Selection.Copy Destination:=Sheets("Output").Range("A1")
Range("A1").Select
End Sub

Open in new window

Regards
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40302849
What do you want to happen if you have 1353 rows, such as in your data?
0
 

Author Closing Comment

by:dallagmm
ID: 40303227
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question