macro for sorting when vlookup used in cells

[IKHAN]

hello,

Using vlookup function for 1000 lines pulling data from different sheets,When i use below MACROS, it takes longer to execute since its looking at the 1000 lines.


Can i get modified code where it looks for only copied data for the day, example 100 items copied in Sheet 1 column A and remove entire ROW if dups found


2. Remove duplicates


Sub RemoveDuplicates()

Dim LastRow As Integer

Application.ScreenUpdating = True

With Worksheets("Sheet1")

LastRow = .Range("A65536").End(xlUp).Row


For i = LastRow To 1 Step -1

If WorksheetFunction.CountIf(.Range(.Cells(LastRow, "A"), .Cells(i, "A")), .Cells(i, "A").Value) > 1 Then

.Cells(i, "A").EntireRow.Delete

End If

Next i

End With

Application.ScreenUpdating = True

End Sub


****Same for EOL its looking for entire 1000 lines and delayed response


'Remove EolRows


Dim lRow As Long

Dim vItem As Variant, vList As Variant

vList = Array("eol")

lRow = 1

Do

For Each vItem In vList

If Cells(lRow, "G") = vItem Then

Cells(lRow, "G").EntireRow.Delete Shift:=xlUp

lRow = lRow - 1

End If

Next vItem

lRow = lRow + 1

Loop While lRow <= Cells(Cells.Rows.Count, "G").End(xlUp).Row

End Sub


**** Need to sort based on copied data, currently have vlookup function in 1000 lines(rows) below macro doesn't work.


Rows start from R5


EXAMPLE NOTE : COULMN F has vlookup function used to pull data from another sheet)

COLUMN A -- COLUMN F

APPLE --- STORE1

ORANGE--- BOBSTORE


OUTPUT REQD in ascending order:

COLUMN A -- COLUMN F

ORANGE--- BOBSTORE

APPLE --- STORE1


6. Sort col f


Sub SoftColF()


Columns("F:F").Select

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F1"), _

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort

.SetRange Selection

.Header = xlNo

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

 

[Luke M]

To start, all of your macros will probably run about 10x faster if you have the line

[pre]

Application.ScreenUpdating = False

[/pre]
at the beginning. This will allow the code to run all the way through w/o having to update the on screen image after ever step.

 

[IKHAN]

Thanks luke !! That does make things go faster..


Need help with sorting


**** Need to sort based on copied data, currently have vlookup function in 1000 lines(rows) below macro you provided doesn't work.


Rows start from R5


EXAMPLE NOTE : COULMN F has vlookup function used to pull data from another sheet in cells,(=IF(ISNA(VLOOKUP(A53,'sheet4'!F:AD,14,FALSE)),"",VLOOKUP(A53,'sheet4'!F:AD,14,FALSE))


COLUMN A -- COLUMN F

APPLE --- STORE1

ORANGE--- BOBSTORE


OUTPUT REQD in ascending order:

COLUMN A -- COLUMN F

ORANGE--- BOBSTORE

APPLE --- STORE1


Sub SoftColF()


Columns("F:F").Select

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F1"), _

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort

.SetRange Selection

.Header = xlNo

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

 

[Luke M]

Try this.

[pre]

Sub SoftColF()

Columns("A:F").Select 'Need to select all the data you want to sort
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Selection
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

[/pre]