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!

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.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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!

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

Thanks,


It works great! I appreciate all your help!

Your welcome.