TheNarddog
New Member
- Joined
- Sep 28, 2022
- Messages
- 5
Hi
On my sheet I have headings on row 2, columns in use from A to I and rows of data from row 3 and below.
Column A is "Date", column B to I aren't significant.
So I could have any number rows of a certain dd/mm/yy, then any number of rows of another dd/mm/yy and it keeps going like this down the sheet.
At the moment I use empty rows to separate different groups of dates.
Another user has helped me massively to produce the following code to sort my data and automate my actions. The way it sorts though gets rid of my gaps and I struggle to easily see the difference between each group of dd/mm/yy that are the same.
So I'm hoping either:
1) There could be someway of using the code below then adding a way to insert an empty row between dd/mm/yy's automatically
or
2) Have the code below sort one group of dd/mm/yy's that are the same date, then I'll already have the empty row for a gap between dates, then the code works again on the next group of dates that are the same.
On my sheet I have headings on row 2, columns in use from A to I and rows of data from row 3 and below.
Column A is "Date", column B to I aren't significant.
So I could have any number rows of a certain dd/mm/yy, then any number of rows of another dd/mm/yy and it keeps going like this down the sheet.
At the moment I use empty rows to separate different groups of dates.
Another user has helped me massively to produce the following code to sort my data and automate my actions. The way it sorts though gets rid of my gaps and I struggle to easily see the difference between each group of dd/mm/yy that are the same.
So I'm hoping either:
1) There could be someway of using the code below then adding a way to insert an empty row between dd/mm/yy's automatically
or
2) Have the code below sort one group of dd/mm/yy's that are the same date, then I'll already have the empty row for a gap between dates, then the code works again on the next group of dates that are the same.
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Me.Range("G3", Cells(Rows.Count, "H").End(3)), Target) Is Nothing Then
Cancel = True
If Target.Column = 7 And Target.Value <> "Yes" Then Target.Value = "Yes"
If Target.Column = 8 Then Target.Value = Format(Now(), "dd/mm/yyyy") '<< change format to suit
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge > 1 Then Exit Sub
Dim LRow As Long
LRow = Cells.Find("*", , xlFormulas, , 1, 2).Row
If Not Intersect(Me.Range("A3:I" & LRow), Target) Is Nothing Then
Application.EnableEvents = False
With Me.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("G2"), Order:=xlAscending
.SortFields.Add Key:=Range("A2"), Order:=xlAscending
.SortFields.Add Key:=Range("F2"), Order:=xlAscending
.SortFields.Add Key:=Range("E2"), Order:=xlAscending
.SetRange Range("A3:I" & LRow)
.Apply
End With
End If
Application.EnableEvents = True
End Sub