VBA dates in a selection of data, not always in the same Column

jislandhopper

Board Regular
Joined
Jul 23, 2013
Messages
92
Hi Everyone,

I’m using Excel 2017 and currently running a Macro which works well for the most part but I was hoping for some direction on how to make it more accurate.

The macro filters all data beyond today’s current date in a helper cell I42 in helper tab ‘Lookup’
It checks against J42 which is todays date -100 days also in helper tab ‘Lookup’.

The dates on the data can vary from days to long periods of time. If work was taking place on the day Col W and X on the ‘data’ tab would be identical. If it’s a duration those dates can are amended accordingly. Col W will always have a date within the selected section of data. Col X is not always populated with a date.

The code below mainly captures all my date. But I have an issue.
On the data tab Col W is the start date and Col X is the finish date. The data from Col X drives the Macro’s reasoning. But if there is a blank cell the selection would stop. Not capturing the full list.
I’m after a way I can select the data from Col X (the finishing date) to run the macro but if that cell is blank refer to Col W. (start date/marker date) and pull the data for that line if over 100 days.

Sub JBHISTORY100()
Sheets("Data").Select
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
Cells.EntireRow.Hidden = False
Dim lngStart As Long, lngEnd As Long
Sheets("Lookup").Select
lngStart = Range("J42").Value
Sheets("Lookup").Select
lngEnd = Range("I42").Value
Sheets("Data").Select
Range("X1:X10000").AutoFilter Field:=24, _
Criteria1:=">=" & lngStart, _
Operator:=xlAnd, _
Criteria2:="<=" & lngEnd
ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range( _
"D1:D10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Data").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Does anyone have a way I can incorporate both scenarios, or tips to get round this?
I would like to avoid using a helper cell on the spreadsheet, as there is a lot of information in various cells utilised by other users.

Hope the above makes sense but let me know if you need more information.
Thanks.

Jason
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top