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