View Full Version : [SOLVED:] Sort data Only A2 to LastCell
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
11-12-2013, 03:29 PM
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
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
11-13-2013, 05:05 AM
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?
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
11-13-2013, 06:48 AM
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
i'm very glag, thank you!!
mancubus
11-13-2013, 12:22 PM
im glad it helped. cheers.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.