.Rows.Count

[Derek McGill]

In this code

Dim myCol As Long
myCol = Rows(1).Find("Games").Column
Application.DisplayAlerts = False
With Intersect(Columns(1).SpecialCells(2).Areas(1).EntireRow, Columns(1).Resize(, myCol).EntireColumn)
.Offset(-1).Resize(.Rows.Count + 1).Copy .Offset(.Rows.Count - 11)
Application.DisplayAlerts = True
With .Offset(.Rows.Count - 11).CurrentRegion.Resize(, myCol)
.Columns(.Columns.Count).Replace ChrW(189), ".5", 2
.Sort .Cells(1, .Columns.Count), 2, , , , , , 1
.Columns(.Columns.Count).Replace ".5", ChrW(189), 2
Union(.Columns(1), .Columns(.Columns.Count)).Copy .Columns(.Columns.Count + 3)
With .Columns(.Columns.Count + 2)
With .Offset(1).Resize(.Rows.Count - 1)
End With
End With
End With
End With
How does " .Rows.Count " get its value ?

 

[Chihiro]

God, that's terrible code structure to read... also can you edit it to put it in side Code tag?

Since it's inside "With" statement. It's equivalent of...

Intersect(Columns(1).SpecialCells(2).Areas(1).EntireRow, Columns(1).Resize(, myCol).EntireColumn).Rows.Count

 

[Derek McGill]

After Intersect(Columns(1).SpecialCells(2).Areas(1).EntireRow, Columns(1).Resize(, myCol).EntireColumn).Rows.Count
I get Error 1004 No cells were found.

 

[Chihiro]

Hmm? Upload your file.

See result from my test.

Worksheet set up.


Code and result.

 

[Derek McGill]

Here is the file

 

[Chihiro]

The reason that you are getting error is that "SpecialCells(2)" looks for cells that contain constants, but all of your column A contains formulas.

 

[jindon]

That code is based on the workbook provided here
https://forum.chandoo.org/threads/sort-colours.32312/

Change

With Intersect(Columns(1).SpecialCells(2).Areas(1).EntireRow, Columns(1).Resize(, myCol).EntireColumn)

to

With Intersect(Columns(1).SpecialCells(-4123, 2).Areas(1).EntireRow, Columns(1).Resize(, myCol).EntireColumn)

 

[Derek McGill]

The SpecialCells change puts a 0 in Vol A instead of the players name.

 

[jindon]

You must remember, if you don't understand the meaning of the code and you need to adjust it, ask to the author, otherwise we think you understand the codes.

Sub Btest()
    Dim myCol As Long
    Application.Calculation = xlCalculationManual
    myCol = Rows(2).Find("#Red").Column
    With Intersect(Columns(1).SpecialCells(-4123, 2).Areas(1).EntireRow, Columns(1).Resize(, myCol).EntireColumn)
        .Offset(-1).Resize(.Rows.Count + 1).Copy
        .Offset(.Rows.Count + 2).Cells(1).PasteSpecial xlPasteValues
        .Offset(.Rows.Count + 2).Cells(1).PasteSpecial xlPasteFormats
        With .Offset(.Rows.Count + 2).CurrentRegion.Resize(, myCol)
            .Sort .Cells(1, .Columns.Count - 1), 2, , .Cells(1, .Columns.Count), 1, , , 1
            .Columns(.Columns.Count - 1).Resize(, 2).Clear
            Union(.Columns(1), .Columns(.Columns.Count - 2)).Copy .Columns(.Columns.Count + 1)
            With .Columns(.Columns.Count)
                With .Offset(1).Resize(.Rows.Count - 1)
                    .Value = Evaluate("row(1:" & .Rows.Count & ")")
                    .CurrentRegion.Borders.Weight = 2
                    .CurrentRegion.BorderAround Weight:=4
                    .Columns(3).NumberFormat = "#.0"
                End With
            End With
        End With
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

Code has been edited.