K0st4din
Well-known Member
- Joined
- Feb 8, 2012
- Messages
- 501
- Office Version
- 2016
- 2013
- 2011
- 2010
- 2007
- Platform
- Windows
Hello, everyone,
I have a macro that in the mentioned worksheet, adds new records in two columns if there is no data already entered.
However, right now, it's adding in column B and column D. I'm trying to change this rearrangement slightly, so that when there's an add after ordering for A-Z, but it grabs the whole range of B:E and sorts things again A-Z from column D, not as it is currently separately for D and B.
I made a macro manually, what I want to happen to me, but I don't know how to keep the same actions of the whole macro, but only change the rearrangement.
I would be grateful for your assistance.
EDIT: Or maybe if it can be changed in the macro itself somehow without adding my idea
I have a macro that in the mentioned worksheet, adds new records in two columns if there is no data already entered.
However, right now, it's adding in column B and column D. I'm trying to change this rearrangement slightly, so that when there's an add after ordering for A-Z, but it grabs the whole range of B:E and sorts things again A-Z from column D, not as it is currently separately for D and B.
I made a macro manually, what I want to happen to me, but I don't know how to keep the same actions of the whole macro, but only change the rearrangement.
I would be grateful for your assistance.
VBA Code:
ActiveSheet.Unprotect "k0"
On Error Resume Next
Dim ws As Worksheet
Dim str As String
Dim i As Integer
Dim rngDV As Range
Dim rng As Range
If Target.CountLarge > 1 Then Exit Sub
Set ws = Worksheets("Base")
If Target.Row > 1 Then
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If rngDV Is Nothing Then Exit Sub
If Intersect(Target, rngDV) Is Nothing Then Exit Sub
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
On Error Resume Next
Set rng = ws.Range(str)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
If Application.WorksheetFunction _
.CountIf(rng, Target.Value) Then
Exit Sub
Else
*******I think the exact same idea should be changed here, but with the macro below (the arrangement I made)****
i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1
ws.Cells(i, rng.Column).Value = Target.Value
rng.Sort Key1:=ws.Cells(1, rng.Column), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
ActiveSheet.Protect "k0"
End Sub
Code:
******This is the macro I made myself and I'm trying
to add(change) it to the above macro in its order*******
Columns("B:E").Select
ActiveWorkbook.Worksheets("Base").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Base").Sort.SortFields.Add Key:=Range("D2:D400"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Base").Sort
.SetRange Range("B2:E400")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B2").Select
EDIT: Or maybe if it can be changed in the macro itself somehow without adding my idea
Last edited by a moderator: