Sort data Only A2 to LastCell

[elsg]

Hellow everybody!

i need sort my table (column A to D), but sort first (A2) to Lastcell with data.


Private Sub Worksheet_Change(ByVal Target As Range)'I try sort A2 to Last cell popupalizaded
LR = Range("A" & Rows.Count).End(xlUp).Row
Set LR = Range("A1:D" & LR)
If Not (Application.Intersect(Worksheets(1).Range("A1:D" & LR), Target) Is Nothing) Then
Worksheets("Plan1").Range("A1:D" & LR).Sort Key1:=Worksheets("Plan1").Range("A2"), Order1:=xlAscending
End If
End Sub

[mancubus]

i think Worksheets(1) and Worksheets("Plan1") and the worksheet with the below event code refer to the same worksheet.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row)
If Not Intersect(rng, Target) Is Nothing Then
rng.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End If
End Sub

[elsg]

Thank you...

works, but I figured it would solve, but does not solve.
In column A has values ​​(numbers), my table has 4 columns (column A to D).


I wish that after entering the number (column A), only after entering the cells of the columns (column A to D), is that the code will sort the data.

Thank you!!

[mancubus]

you are welcome. i dont understand your requirement. you mean the range between target cell's row and the last row of the table will be sorted (sort by rows)? or only target cell's row will be sorted (sort by columns)? or something else?

[elsg]

I'll try to explain better



Cod
Nome
Value
Tip



37
MARK
321
OK



54
JOHN
457
OK



86
KLEBER
545
OK







<<--before entering the code














Cod
Nome
Value
Tip



1



after pressing the ENTER key


37
MARK
321
OK



54
JOHN
457
OK



86
KLEBER
545
OK



Here
<<--The cursor is still here


















I have two options, only sort after data preecher data in the last cell (Column A to D).


or go with the cursor after the data classification.
look

before entering the data (Column A to D)


Cod
Nome
Value
Tip


37
MARK
321
OK


54
JOHN
457
OK


86
KLEBER
545
OK


1
MANCUBUS
124
OK












After the data is entered (Column A to D).


Cod
Nome
Value
Tip


1
MANCUBUS
124
OK


37
MARK
321
OK


54
JOHN
457
OK


86
KLEBER
545
OK

[mancubus]

try this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Set rng = Range("A1:D" & LastRow)
If Not Intersect(rng, Target) Is Nothing Then
If Application.CountA(Range(Cells(Target.Row, 1), Cells(Target.Row, 4))) = 4 Then
'to ensure values are entered in cols A to D
rng.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
Cells(LastRow, 1).Select 'go to last blank cell in col A
End If
End If
End Sub

[elsg]

i'm very glag, thank you!!

[mancubus]

im glad it helped. cheers.