Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sample Size as Series Data Label | Charts and Charting in Excel | |||
Display sample population size in chart i.e. n=X | Charts and Charting in Excel | |||
Sample Size and Confidence Interval | Excel Discussion (Misc queries) | |||
In a yes or no situation how do I choose a sample size | Excel Discussion (Misc queries) | |||
Determine Valid Sample Size from a Population - Any UDFs or Formulas? | Excel Programming |