Need help with selecting a Range....

  • Thread starter Thread starter RompStar
  • Start date Start date
R

RompStar

Ok, imagine that I have a Column B:

The data range starts at A9 and below, above A9 are headers.

And imagine that this Columns in the data range stores dates.

Imagine that the date data is for all of the month of July 2005

so,

A9: 07-01-2005
A10: 07-01-2005
A11: 07-02-2005
A12: 07-02-2005 ( yes some rows might have the same date more then
once... )

and so on..

so if I want to write a VB script, and I want to ask the user for a
Starting and Ending Range using a VB popup window (which I learned how
to do). So, say that I want to select only the dates
between 7-5-2005 and 7-20-2005, and at the same time I want to be able
to count the number of date instances that I have, where:

1 date = 1 count, like 07-01-2005 = 1

so if the Range has 125 dates, the VB script would know that and store
the count value.

then there are other things that I need to do, but I want to learn this
one first, any help appreciated.

Thanks!!!
 
Here's a start:
Copies the data to a temporary tab for processing (just-in-case since cant
undo macros) then filters to your desired date range, counting the selected
rows.
Replace BOM/EOM values with your popup results (BOM auto-finds the first day
of last month while EOM auto-finds the last day of last month in case this
is useful).
-----
Sub test()
'copy raw data to a working space
Sheets("raw").Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "test"
'set variables used
includedrows = 0
BOM = ">=" & DateSerial(Year(Now), Month(Now) - 1, 1)
EOM = "<=" & DateSerial(Year(Now), Month(Now), 0)
With Range("A9:A" & Range("A65536").End(xlUp).Row)
'select desired rows
.NumberFormat = "mm-dd-yy"
.AutoFilter Field:=1, Criteria1:=BOM, Operator:=xlAnd, Criteria2:=EOM
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible).EntireRow.Select
'count selected rows
includedrows = Selection.Rows.Count
End With
ActiveSheet.AutoFilterMode = False 'turn off autofilter
Range("B1").Value = includedrows
End Sub
 
Back
Top