Macro help

[VBABEGINER]

Hi All, Long back again..

need help in terms of vba code.

I've created one column with the help of Rank formula. Now I want any 2 records of every rank number on another sheet. Randomly. any code can I get please..

 

[VBABEGINER]

You may try such ammended code:

Sub test2()
Dim lr As Long
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.AutoFilterMode = False
If ActiveSheet.ListObjects.Count > 0 Then ActiveSheet.ListObjects(1).Unlist
lr = Cells(Rows.Count, "BN").End(xlUp).Row
Columns("BQ:BQ").Insert Shift:=xlToRight
Range("BM2:BP" & lr).Sort Key1:=Range("BN2"), Key2:=Range("BP2"), Order2:=xlAscending, Header:=xlNo, Orientation:=xlSortColumns
Range("BQ2:BQ" & lr).FormulaR1C1 = "=COUNTIFS(R1C[-3]:RC[-3],RC[-3],R1C[-1]:RC[-1],""consider"")"
Range("BQ2:BQ" & lr).Value = Range("BQ2:BQ" & lr).Value
Range("BP1:BQ" & lr).AutoFilter Field:=2, Criteria1:="0", Operator:=xlOr, Criteria2:=">2"
Rows("2:" & lr).Delete Shift:=xlUp
ActiveSheet.AutoFilterMode = False
Range("BP1:BQ" & lr).AutoFilter Field:=1, Criteria1:="<>consider"
Rows("2:" & lr).Delete Shift:=xlUp
ActiveSheet.AutoFilterMode = False
Columns("BQ:BQ").Delete Shift:=xlToLeft
End Sub

Hi Kaper, I checked and share some error happening. Could you pls suggest on this..

 

[Kaper]

Of course I have no foggiest idea what ranges were in second CONCAT in column BO. For sure not to the same row. You may convert formulas to values before sorting, as they wil be probably no longer need to be recalculated.

Range("BM2:BP" & lr).value = Range("BM2:BP" & lr).value

would do the job.
so the code would look like:

Sub test3()
Dim lr As Long
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.AutoFilterMode = False
If ActiveSheet.ListObjects.Count > 0 Then ActiveSheet.ListObjects(1).Unlist
lr = Cells(Rows.Count, "BN").End(xlUp).Row
Columns("BQ:BQ").Insert Shift:=xlToRight
Range("BM2:BP" & lr).value = Range("BM2:BP" & lr).value
Range("BM2:BP" & lr).Sort Key1:=Range("BN2"), Key2:=Range("BP2"), Order2:=xlAscending, Header:=xlNo, Orientation:=xlSortColumns
Range("BQ2:BQ" & lr).FormulaR1C1 = "=COUNTIFS(R1C[-3]:RC[-3],RC[-3],R1C[-1]:RC[-1],""consider"")"
Range("BQ2:BQ" & lr).Value = Range("BQ2:BQ" & lr).Value
Range("BP1:BQ" & lr).AutoFilter Field:=2, Criteria1:="0", Operator:=xlOr, Criteria2:=">2"
Rows("2:" & lr).Delete Shift:=xlUp
ActiveSheet.AutoFilterMode = False
Range("BP1:BQ" & lr).AutoFilter Field:=1, Criteria1:="<>consider"
Rows("2:" & lr).Delete Shift:=xlUp
ActiveSheet.AutoFilterMode = False
Columns("BQ:BQ").Delete Shift:=xlToLeft
End Sub

 

[VBABEGINER]

Of course I have no foggiest idea what ranges were in second CONCAT in column BO. For sure not to the same row. You may convert formulas to values before sorting, as they wil be probably no longer need to be recalculated.

Range("BM2:BP" & lr).value = Range("BM2:BP" & lr).value

would do the job.
so the code would look like:

Sub test3()
Dim lr As Long
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.AutoFilterMode = False
If ActiveSheet.ListObjects.Count > 0 Then ActiveSheet.ListObjects(1).Unlist
lr = Cells(Rows.Count, "BN").End(xlUp).Row
Columns("BQ:BQ").Insert Shift:=xlToRight
Range("BM2:BP" & lr).value = Range("BM2:BP" & lr).value
Range("BM2:BP" & lr).Sort Key1:=Range("BN2"), Key2:=Range("BP2"), Order2:=xlAscending, Header:=xlNo, Orientation:=xlSortColumns
Range("BQ2:BQ" & lr).FormulaR1C1 = "=COUNTIFS(R1C[-3]:RC[-3],RC[-3],R1C[-1]:RC[-1],""consider"")"
Range("BQ2:BQ" & lr).Value = Range("BQ2:BQ" & lr).Value
Range("BP1:BQ" & lr).AutoFilter Field:=2, Criteria1:="0", Operator:=xlOr, Criteria2:=">2"
Rows("2:" & lr).Delete Shift:=xlUp
ActiveSheet.AutoFilterMode = False
Range("BP1:BQ" & lr).AutoFilter Field:=1, Criteria1:="<>consider"
Rows("2:" & lr).Delete Shift:=xlUp
ActiveSheet.AutoFilterMode = False
Columns("BQ:BQ").Delete Shift:=xlToLeft
End Sub

Hi Kaper, Thank you so much for your reply. I didnt get a chance to cross check the same. I will ensure that either tomorrow EOD or day after tomorrow I will confirm you back.. Till then Thanks again