Need to save a range of cells into a variable and make the count show up in a MsgBox

[Klash Ville]

title says it all, here's what I have now, but for some reason, it's not working when it should...

    Range("A3").Select
    ActiveCell.Offset(1, 0).Activate


    Dim tbl As Range
    Set tbl = Range(Selection, Selection.End(xlDown)).Select
    
    MsgBox tbl.Rows.Count

 

[Joe4]

Remove the "Select" from your Set statement, i.e.

    Set tbl = Range(Selection, Selection.End(xlDown))

Also, what is the purpose of this?

    Range("A3").Select
    ActiveCell.Offset(1, 0).Activate

That could just be simplified to:

    Range("A4").Select

Actually, your whole code block could be simplified to this:

    Dim tbl As Range
    Set tbl = Range(Range("A4"), Range("A4").End(xlDown))
    
    MsgBox tbl.Rows.Count

 

[Klash Ville]

Thanks, I adapted most of your simplified code into the already existing one, and it worked just like I wanted.

Here's the final result in case you are curious:

    Sheets("Feuil1").Select    Cells.Find(What:="Nom", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Activate
    Dim tbl1 As Range
    Set tbl1 = Range(Selection, Selection.End(xlDown))
    
    Sheets("Country").Select
    Cells.Find(What:="Nom", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Activate
    Dim tbl2 As Range
    Set tbl2 = Range(Selection, Selection.End(xlDown))
    
    If tbl1.Rows.Count <> tbl2.Rows.Count Then
        MsgBox ("Based on the mutual column 'Nom' in the sheets 'Feuil1' and 'Country', the total number of rows is NOT equal..." & vbNewLine & vbNewLine & _
        "It has been detected " & tbl1.Rows.Count & " row(s) in the sheet 'Feuil1'" & vbNewLine & _
        "It has been detected " & tbl2.Rows.Count & " row(s) in the sheet 'Country'"), vbCritical
        
        Exit Sub
        End If

Also, is there a smaller and simplified way in Excel VBA for it to select a cell based on a certain real, integer or string value rather than using the entire Search feature?

 

[Joe4]

Also, is there a smaller and simplified way in Excel VBA for it to select a cell based on a certain real, integer or string value rather than using the entire Search feature?

Not that I know of. You could loop through all cells to look for it, but that is a very inefficient way of doing it (loops, by nature, are inefficient).