Show in MSgBox all nonempty values from row

Question

I am working on an Excel sheet of row size A:ZZ and there should only be 7 cells with values. I am trying to make a kind of "filter" that will check the number of non empty cells and in case when there will be more than 7 nonempty cells will print a message in MsgBox (and till this point is working). But in the MsgBox I would like to se also just those values from the row (separated eg. with coma) - this is although not working due to some problem with Intersect syntax. Here is the code

Sub blanks() 
  Dim a, b As Integer
  a = 0

  Range("A1").Select
  Do
    With ActiveSheet.Range(Rows(b))
      b = ActiveCell.Row
      a = Application.WorksheetFunction.CountA(ActiveSheet.Rows(b))

      If a > 7 Then
        MsgBox ("ERROR" & "/n" & Application.Intersect(.SpecialCells(xlCellTypeVisible)))
        Exit Do
      Else
        ActiveCell.Offset(1, 0).Select
      End If

    End With
  Loop Until ActiveCell = "stop"
End Sub

What is there wrong?

while it should not work (at `With ActiveSheet.Range(Rows(b))` cus `b` is `0`) i still would suggest using `.SpecialCells(xlCellTypeVisible).Address`. this should show the ranges which are nonempty (not the values)... if you need the values, you would need a `For Each ...` loop... (no need for `Application.Intersect` at all) — Dirk Reichel, Feb 09 '16 at 08:35

score 0 · Accepted Answer · answered Feb 09 '16 at 09:01

This will give you the constant values (i.e. not from a formula):

Sub ShowValues()
    Dim rowNum As Long
    Dim rowRange As Range
    Dim valRange As Range
    Dim msg As String

    rowNum = 1
    With ThisWorkbook.Worksheets("Sheet1")
        Do While .Cells(rowNum, 1) <> "stop"
            Set rowRange = .Cells(rowNum, 1).Resize(1, 702)
            If Application.WorksheetFunction.CountA(rowRange) > 7 Then

                'Add row number to message.
                msg = msg & "Row: " & rowRange.Row & ": "

                'Add values to message separated by comman.
                For Each valRange In rowRange.SpecialCells(xlCellTypeConstants)
                    msg = msg & valRange.Value & ", "
                Next valRange

                'Remove last comma.
                msg = Left(msg, Len(msg) - 2)

                'Line break.
                msg = msg & vbCr
            End If
            rowNum = rowNum + 1
        Loop
    End With
    MsgBox msg, vbOKOnly + vbInformation
End Sub

or this will give you the addresses:

Sub ShowAddressOfValues()
    Dim rowNum As Long
    Dim rowRange As Range
    Dim msg As String

    rowNum = 1
    With ThisWorkbook.Worksheets("Sheet1")
        Do While .Cells(rowNum, 1) <> "stop"
            Set rowRange = .Cells(rowNum, 1).Resize(1, 702)
            If Application.WorksheetFunction.CountA(rowRange) > 7 Then
                msg = msg & "Row: " & rowRange.Row & ". Address: " & _
                    rowRange.SpecialCells(xlCellTypeConstants).Address & vbCr
            End If
            rowNum = rowNum + 1
        Loop
    End With
    MsgBox msg, vbOKOnly + vbInformation
End Sub

This is basically exactly what I needed but the with loop always ends with some message (also empty). How in this way make something like: `if there is anywhere more than those 7 values than print a message and if not just continue with ending this function (go on)?` — vonski, Feb 15 '16 at 09:55

Show in MSgBox all nonempty values from row

1 Answers1