I have this macro that works fine, the only problem is it hides the rows I don't want and when I try to copy and paste the cells left behind to a new worksheet it brings everything. Can anyone help?

Also isn't paste special supossed to allow me to only paste the unhidden rows?

Thanks

Sub SortCaseCapRd()

BeginRow = 1

EndRow = 40000

ChkCol = 8



For RowCnt = BeginRow To EndRow

If Cells(RowCnt, ChkCol) = UCase(Cells(RowCnt, ChkCol)) Then

Cells(RowCnt, ChkCol).EntireRow.Hidden = True

Else

Cells(RowCnt, ChkCol).EntireRow.Hidden = False

Cells(RowCnt, ChkCol).Font.ColorIndex = 3

Cells(RowCnt, ChkCol).Font.Bold = True

For Each cell In Cells(RowCnt, ChkCol)

cell.Formula = UCase(cell.Formula)

Next

End If

Next RowCnt

End Sub