Script to Select a Range Between Two Given Values

[Weatherman]

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!

 

[Weatherman]

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.

 

[Rick Rothstein]

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?

 

[Weatherman]

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).

Thanks for your help!

Jason

 

[Weatherman]

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?

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("A1:B38032").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    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

 

[shg]

Starting with this,

       -----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:

Sub WM()
    With Worksheets("Raw Barometric Data")
        .Range("A4").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, _
                                                  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.

 

[Weatherman]

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

 

[shg]

You're welcome, good luck.