当前位置: 动力学知识库 > 问答 > 编程问答 >

excel vba output all data in active cells in column A to a msgbox

问题描述:

how do i output all the active cells in for instance column "A" to a msgbox

example file

chicken stew

fish pie

apple cake

msgbox would display

chicken

fish

apple

this code works

MsgBox Worksheets("Sheet1").Range("A2").Value & vbCrLf & Worksheets("Sheet1").Range("A3")

but this does not

MsgBox Worksheets("Sheet1").Range("A:A").Value

many thanks

网友答案:

how would i get this to not display a message box if column A is empty

Try this one

Sub test()
    Dim lastrow As Long, res As String
    With Worksheets("Sheet1")
        If WorksheetFunction.CountA(.Range("A:A")) = 0 Then
            MsgBox "Column A is empty"
            Exit Sub
        End If
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        res = Join(Application.Transpose(.Range("A2:A" & lastrow + 1).Value), vbCrLf)
        Do While InStr(1, res, vbCrLf & vbCrLf) ' remove extra vbCrLf (if there're empty rows)
            res = Replace(res, vbCrLf & vbCrLf, vbCrLf)
        Loop
        MsgBox Left(res, Len(res) - 1)
    End With
End Sub
网友答案:

Consider:

Sub dural()
    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        If r.Value <> "" Then
            msg = msg & vbCrLf & r.Value
        End If
    Next r
    MsgBox msg
End Sub

EDIT#1:

This version tests if column A is empty:

Sub dural()
    If Application.WorksheetFunction.CountA(Range("A:A")) = 0 Then
        MsgBox "Column A is empty"
        Exit Sub
    End If
    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        If r.Value <> "" Then
            msg = msg & vbCrLf & r.Value
        End If
    Next r
    MsgBox msg
End Sub
分享给朋友:
您可能感兴趣的文章:
随机阅读: