# Script to Select a Range Between Two Given Values

#### Weatherman

##### New Member
Hello All...

I'm trying to figure out how to get Excel to select or go to all data in columns A and B of sheet 1 that are equal or between two given values. When it finds this data, I'd like it to copy it and paste it into sheet 2. Column A is the date/time in numeric format to 6 decimal places. Column B is a numeric value to 5 decimal places. In cell C1 I have the beginning of where I would like the data to be selected and in cell D1 I have the end of the range. A sample of my data is below:

A B C D
41059.822356 29.70051 40160 40161
41060.215632 29.90145
41060.826352 29.89514
41061.000125 29.84120

In this case...the script would look in column A for all values greater than or equal to 40160 and less than or equal to 40161 and select both the values in column A and its corresponding value in column B (A2:B3 in this case).

I'm running Excel 2010 on Windows 7. Any help would be greatly appreciated. Thank you for your time.

Weatherman!

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
A minor correction to the sample data for column A above. That data should be:

40159.822356
40160.215632
40160.826352
40161.000125

My apologies for this error.

A minor correction to the sample data for column A above. That data should be:

40159.822356
40160.215632
40160.826352
40161.000125

My apologies for this error.
Will the values in Column A always be sorted (as you show in your example), meaning a contiguous range with be selected? Or will the values in Column A be unsorted, meanin a non-contiguous range will be selected?

Will the values in Column A always be sorted (as you show in your example), meaning a contiguous range with be selected? Or will the values in Column A be unsorted, meanin a non-contiguous range will be selected?

Rick...

Column A will always be in order. It is a date/time down to the hundredth of a second. Column B will be variable. It is a pressure reading. Cell C1 and D1 will vary based on user input. The user will enter a date in mm/dd/yyyy format and it will be converted to a numeric value in C1. D1 is C1+1. The script will need to find and select all of the column A data (date time) and its associated pressure data (column B) between C1 and D1 (or for that date).

Jason

Ok...I figured out how to filter the data properly using the advanced filter option...and I tried to automate this by recording a macro. The macro works up until the point where it is supposed to use the advanced filter. I get a "400 Error" and it does not apply the advanced filter...and therefore copies nothing. Below is the macro code. Any suggestions as to the cause of this?

Code:
``````Sub GetData()
' GetData Macro
Application.Goto Reference:="R9C1:R5770C2"
Selection.ClearContents
Range("A1").Select
Sheets("Raw Barometric Data").Select
Application.Goto Reference:="R2C5:R5770C6"
Selection.ClearContents
Range("C1:C2"), CopyToRange:=Range("E2:F2"), Unique:=False
Application.Goto Reference:="R3C5:R5770C6"
Selection.Copy
Sheets("Chart Barometric Data").Select
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
End Sub``````

Thanks for any help!

Jason

Starting with this,

Code:
``````       -----A------ ---B----
1       Time       Time
2   >=40160      <40161
3
4       Time     Pressure
5   40159.110069 28.57705
6   40159.581341 29.61035
7   40159.590996 28.95281
8   40159.761650 28.22110
9   40159.952563 28.65261
10   40159.999806 28.61519
11   40160.029822 29.33929
12   40160.087385 29.85874
13   40160.120413 28.25024
14   40160.184820 29.73559
15   40160.208728 29.73016
16   40160.212247 28.30725
17   40160.425711 29.25087
18   40160.435997 28.39146
19   40160.486166 28.47268
20   40160.620621 28.84070
21   40160.716948 29.70008
22   40160.723294 29.43088
23   40160.882528 28.63600
24   40160.939089 28.72548
25   40160.994084 28.86381
26   40161.045478 28.46009
27   40161.106700 28.46702
28   40161.178632 29.65701
29   40161.179144 28.61705
30   40161.509094 29.71825
31   40161.513804 29.57381
32   40161.701861 29.30029
33   40161.980239 29.05025
34   40162.089107 28.85010``````

This worked for me:

Code:
``````Sub WM()
With Worksheets("Raw Barometric Data")
CriteriaRange:=.Range("A1:B2"), _
Unique:=False
End With
End Sub``````

Note that there is a blank row between the data range and the criteria range, and the header in the criteria range matches the header in the data.

Thanks for all the help. I got it to work!

You're welcome, good luck.

Replies
3
Views
749
Replies
7
Views
666
Replies
2
Views
449
Replies
7
Views
452
Replies
5
Views
273

1,203,069
Messages
6,053,351
Members
444,655
Latest member
didr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back