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