Sample Size
 

Does anyone have any sugggestions for:

I have a spreadsheet that generates random numbers based on a range of
dates. This result can be 100, 500, etc. rows of data. From the rows
of data that are generated, I may select the first 25 rows of data out
of 100 possible rows. The next day, I may select 50 rows out of 500
possible rows.

For example:

The Sample size (25) is on Sheet 1 - Cell B3
On Sheet 3 is 150 rows of data (beginning on Cell A2).

Is there a way to write a macro that will count the rows (in this case
Sheeet 3-A2 through Sheet 3-A26 - 25 Rows) and delete all rows beneath
A26 on Sheet 3?

Since the sample size changes daily is there a way to reference Sheet 1
- Cell B3 so that if the sample size is 50, the macro will keep 50 rows
of data and delete the rest, etc.

Thanks for the help!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=390384

Sample Size
 

I'm assuming that you will be re-generating all the rows on Sheet 3 each
day before running the macro and that your data will always start in
A2.

Sub SampleSize()
Sheets(3).Range("A" & Sheets(1).Range("B3").Value + 2 & ":A" & _
Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row).Delete
End Sub

Hope this does what you are after.


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=390384

Sample Size
 

Dim lrw as Long, x as long

lrw = Sheets("Sheet3").Cells(Rows.COUNT, "A").End(xlUp).Row ' # rows in
sheet3
x = Sheets("Sheet1").Range("B3") ' number of rows to keep

' since row1 contains a header (or something)
x = x+2 ' 1st row to delete (x+1 = last row to keep)

With Sheets("Sheet3")
.Range(Rows(x),Rows(lrw)).Delete
End With

--
steveB

Remove "AYN" from email to respond
"STEVEB" wrote in
message ...

Does anyone have any sugggestions for:

I have a spreadsheet that generates random numbers based on a range of
dates. This result can be 100, 500, etc. rows of data. From the rows
of data that are generated, I may select the first 25 rows of data out
of 100 possible rows. The next day, I may select 50 rows out of 500
possible rows.

For example:

The Sample size (25) is on Sheet 1 - Cell B3
On Sheet 3 is 150 rows of data (beginning on Cell A2).

Is there a way to write a macro that will count the rows (in this case
Sheeet 3-A2 through Sheet 3-A26 - 25 Rows) and delete all rows beneath
A26 on Sheet 3?

Since the sample size changes daily is there a way to reference Sheet 1
- Cell B3 so that if the sample size is 50, the macro will keep 50 rows
of data and delete the rest, etc.

Thanks for the help!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=390384

Sample Size
 

Thanks for all your help.

This worked deleting the contents in column A:

Sub SampleSize()
Sheets(3).Range("A" & Sheets(1).Range("B3").Value + 2 & ":A" & _
Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row).Delete
End Sub

Is there a way to delete the entire row rather than just deletin
column A?
Thanks again

--
STEVE
-----------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187
View this thread: http://www.excelforum.com/showthread.php?threadid=39038

Sample Size
 

Sub SampleSize()
Sheets(3).Range("A" & Sheets(1).Range("B3").Value + 2 & ":A" & _
Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row).EntireRow.Delete
End Sub
--
Regards,
Tom Ogilvy


"STEVEB" wrote in
message ...

Thanks for all your help.

This worked deleting the contents in column A:

Sub SampleSize()
Sheets(3).Range("A" & Sheets(1).Range("B3").Value + 2 & ":A" & _
Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row).Delete
End Sub

Is there a way to delete the entire row rather than just deleting
column A?
Thanks again!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=390384

Sample Size
 

Sorry, I didn't see that you wanted to delete the entire row.

Use this instead:

Sub SampleSize()
Sheets(3).Range(Rows(Sheets(1).Range("B3").Value + 2), _
Rows(Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row)).Delete
End Sub

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=390384

Sample Size
 

Thanks,


It works great! I appreciate all your help

--
STEVE
-----------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187
View this thread: http://www.excelforum.com/showthread.php?threadid=39038

Sample Size
 

Your welcome. :

--
Tom Ogilv
-----------------------------------------------------------------------
Tom Ogilvy's Profile: http://www.excelforum.com/member.php...nfo&userid=196
View this thread: http://www.excelforum.com/showthread.php?threadid=39038