none
how to find non blank cell in a specific column RRS feed

  • Question

  • Hi,

    I want to get the row number of each non empty cell contains in Column A. For example if I have data in A1,A5,A10,A20 cell system should display 1,5,10,20 respectively through message box.

    Any help would be highly appreciated.

    Thank You.



    • Edited by Deb_chatt Thursday, July 2, 2015 8:20 PM
    Thursday, July 2, 2015 8:19 PM

Answers

  • Hello Deb:

    Here's some code that should take care of that!  I tested it before posting it.

    Option Explicit
    
    Public Sub ListNonBlankCells()
    Dim lngLastRowInColumnA As Long
    Dim wkbDataWorkbook As Workbook
    Dim wksDataWorksheet As Worksheet
    Dim rngSearchRange As Range
    Dim C As Range
    Dim strCellsWithData As String
    Dim lngLenthOfString As Long
    
    
    Set wkbDataWorkbook = ThisWorkbook
    Set wksDataWorksheet = wkbDataWorkbook.Sheets(1)
    
    lngLastRowInColumnA = wksDataWorksheet.Cells(Rows.Count, "A").End(xlUp).Row
    Set rngSearchRange = Range(wksDataWorksheet.Cells(1, "A"), wksDataWorksheet.Cells(lngLastRowInColumnA, "A"))
    
    For Each C In rngSearchRange
        If C.Value <> "" Then
            strCellsWithData = strCellsWithData & CStr(C.Row) & ","
        End If
    Next C
    
    lngLenthOfString = Len(strCellsWithData)
    strCellsWithData = Left(strCellsWithData, lngLenthOfString - 1)
    
    MsgBox ("Cells With Data Are " & strCellsWithData)
    
    End Sub
    

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    • Proposed as answer by RichLocus Thursday, July 2, 2015 11:09 PM
    • Marked as answer by Deb_chatt Friday, July 3, 2015 6:13 PM
    Thursday, July 2, 2015 8:54 PM
  • ALT + F11

    Insert -> Module

    Paste the code below

    F5 to run.

    Sub lookValues()
    
    Dim intLastRow As Variant
    Dim strValues As String
    Dim j As Integer
    
    strValues = ""
    
    Sheets("Sheet1").Range("A1").Select
    
    intLastRow = ActiveCell.SpecialCells(xlLastCell).Row
    
    For i = 1 To intLastRow
        If Cells(i, 1).Value <> "" And Not IsNull(Cells(i, 1).Value) Then
            strValues = strValues & ", " & Cells(i, 1).Row
        End If
    Next i
    
    MsgBox strValues
    
    End Sub
    

    • Proposed as answer by André Santo Thursday, July 2, 2015 9:00 PM
    • Marked as answer by Deb_chatt Friday, July 3, 2015 6:14 PM
    Thursday, July 2, 2015 8:59 PM
  • Assuming that the sheet containing the data is the active sheet, and that Column A contains the data, maybe..

    Option Explicit
    
    Sub ListNonBlankCells()
    
        Dim v As Variant
        Dim s As String
        Dim LastRow As Long
        
        On Error GoTo ErrHandler
        
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
        v = Evaluate("IF(LEN(A1:A" & LastRow & ")>0,"",""&ROW(A1:A" & LastRow & "),"",x"")")
        
        s = Mid(Replace(Join(Application.Transpose(v), ""), ",x", ""), 2)
        
        MsgBox s, vbInformation
        
        Exit Sub
        
    ErrHandler:
        MsgBox "Error " & Err.Number & ":  " & Err.Description
        
    End Sub

    Note that the number of cells in the range being evaluated cannot exceed 65,536 due to the Transpose function, otherwise an error will occur.  Also, only the first 1,023 characters of the resulting string will be displayed in the message box.

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Deb_chatt Friday, July 3, 2015 6:14 PM
    Friday, July 3, 2015 4:19 AM
  • Deb,

    Perhaps this will do the job for you:

    Sub FindConstants()
        Dim rng As Range
        Dim rngC As Range
        Dim sh As Worksheet
        Dim strNE As String
        Set sh = ActiveSheet
        Set rngC = sh.Range("A:A").SpecialCells(xlCellTypeConstants)
        For Each rng In rngC
            strNE = strNE & " " & rng.Address(False, False)
        Next
        [D1] = Replace(Trim(strNE), " ", ", ")
    End Sub

    The same can be done for Formulas

    And to show only the rownumbers:

    Sub FindConstants()
        Dim rng As Range
        Dim rngC As Range
        Dim sh As Worksheet
        Dim strNE As String
        Set sh = ActiveSheet
        Set rngC = sh.Range("A:A").SpecialCells(xlCellTypeConstants)
        For Each rng In rngC
            strNE = strNE & " " & rng.Row 
        Next
        [D1] = Replace(Trim(strNE), " ", ", ")
    End Sub

    Jan


    • Edited by jgkzdl Friday, July 3, 2015 6:46 AM
    • Marked as answer by Deb_chatt Friday, July 3, 2015 6:14 PM
    Friday, July 3, 2015 6:26 AM

All replies

  • Hello Deb:

    Here's some code that should take care of that!  I tested it before posting it.

    Option Explicit
    
    Public Sub ListNonBlankCells()
    Dim lngLastRowInColumnA As Long
    Dim wkbDataWorkbook As Workbook
    Dim wksDataWorksheet As Worksheet
    Dim rngSearchRange As Range
    Dim C As Range
    Dim strCellsWithData As String
    Dim lngLenthOfString As Long
    
    
    Set wkbDataWorkbook = ThisWorkbook
    Set wksDataWorksheet = wkbDataWorkbook.Sheets(1)
    
    lngLastRowInColumnA = wksDataWorksheet.Cells(Rows.Count, "A").End(xlUp).Row
    Set rngSearchRange = Range(wksDataWorksheet.Cells(1, "A"), wksDataWorksheet.Cells(lngLastRowInColumnA, "A"))
    
    For Each C In rngSearchRange
        If C.Value <> "" Then
            strCellsWithData = strCellsWithData & CStr(C.Row) & ","
        End If
    Next C
    
    lngLenthOfString = Len(strCellsWithData)
    strCellsWithData = Left(strCellsWithData, lngLenthOfString - 1)
    
    MsgBox ("Cells With Data Are " & strCellsWithData)
    
    End Sub
    

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    • Proposed as answer by RichLocus Thursday, July 2, 2015 11:09 PM
    • Marked as answer by Deb_chatt Friday, July 3, 2015 6:13 PM
    Thursday, July 2, 2015 8:54 PM
  • ALT + F11

    Insert -> Module

    Paste the code below

    F5 to run.

    Sub lookValues()
    
    Dim intLastRow As Variant
    Dim strValues As String
    Dim j As Integer
    
    strValues = ""
    
    Sheets("Sheet1").Range("A1").Select
    
    intLastRow = ActiveCell.SpecialCells(xlLastCell).Row
    
    For i = 1 To intLastRow
        If Cells(i, 1).Value <> "" And Not IsNull(Cells(i, 1).Value) Then
            strValues = strValues & ", " & Cells(i, 1).Row
        End If
    Next i
    
    MsgBox strValues
    
    End Sub
    

    • Proposed as answer by André Santo Thursday, July 2, 2015 9:00 PM
    • Marked as answer by Deb_chatt Friday, July 3, 2015 6:14 PM
    Thursday, July 2, 2015 8:59 PM
  • Assuming that the sheet containing the data is the active sheet, and that Column A contains the data, maybe..

    Option Explicit
    
    Sub ListNonBlankCells()
    
        Dim v As Variant
        Dim s As String
        Dim LastRow As Long
        
        On Error GoTo ErrHandler
        
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
        v = Evaluate("IF(LEN(A1:A" & LastRow & ")>0,"",""&ROW(A1:A" & LastRow & "),"",x"")")
        
        s = Mid(Replace(Join(Application.Transpose(v), ""), ",x", ""), 2)
        
        MsgBox s, vbInformation
        
        Exit Sub
        
    ErrHandler:
        MsgBox "Error " & Err.Number & ":  " & Err.Description
        
    End Sub

    Note that the number of cells in the range being evaluated cannot exceed 65,536 due to the Transpose function, otherwise an error will occur.  Also, only the first 1,023 characters of the resulting string will be displayed in the message box.

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Deb_chatt Friday, July 3, 2015 6:14 PM
    Friday, July 3, 2015 4:19 AM
  • Deb,

    Perhaps this will do the job for you:

    Sub FindConstants()
        Dim rng As Range
        Dim rngC As Range
        Dim sh As Worksheet
        Dim strNE As String
        Set sh = ActiveSheet
        Set rngC = sh.Range("A:A").SpecialCells(xlCellTypeConstants)
        For Each rng In rngC
            strNE = strNE & " " & rng.Address(False, False)
        Next
        [D1] = Replace(Trim(strNE), " ", ", ")
    End Sub

    The same can be done for Formulas

    And to show only the rownumbers:

    Sub FindConstants()
        Dim rng As Range
        Dim rngC As Range
        Dim sh As Worksheet
        Dim strNE As String
        Set sh = ActiveSheet
        Set rngC = sh.Range("A:A").SpecialCells(xlCellTypeConstants)
        For Each rng In rngC
            strNE = strNE & " " & rng.Row 
        Next
        [D1] = Replace(Trim(strNE), " ", ", ")
    End Sub

    Jan


    • Edited by jgkzdl Friday, July 3, 2015 6:46 AM
    • Marked as answer by Deb_chatt Friday, July 3, 2015 6:14 PM
    Friday, July 3, 2015 6:26 AM