mardi 23 janvier 2018

How to set .Count function to exclude headers?

I am trying to make a sub where the macro counts all the rows containing data (it isn't a set amount, it varies) and then picks a random number between 6 (to exclude headers) and the number of counted rows and highlights the corresponding row. Unfortunately the code I've been working on doesn't do what I need it to do. It does select and highlight a random row, but it selects empty rows too, not only the ones with data. Any ideas where I went wrong?

Sub RandomRow()
Dim mrg As Worksheet
Dim Count As Integer, myRange As Range
Set mrg = ActiveWorkbook.Sheets("Merged")


    mrg.Range("A6:K200000").Interior.Color = RGB(255, 255, 255) 'reset cell colours

        Set myRange = mrg.Columns("A:A")
        Count = Application.WorksheetFunction.CountA(myRange)

myValue = Int((Count * Rnd) + 6)    ' Generate random value between 6 and the number of filled rows'.

mrg.Range("A" & myValue).EntireRow.Interior.Color = RGB(255, 255, 153) 'highlight a random row

End Sub




Aucun commentaire:

Enregistrer un commentaire