locked
Find range based on string search C# RRS feed

  • Question

  • Hello Everyone, 

    I have an Excel worksheet with huge data. 

    There is a specific column with only strings in them . 

    Form example  I have a excelSheet as follows:

    Header1  Header2 Header3 Header4
    234 Jake AAA 4/11/2012
    212 Tom AAA 9/3/2015
    893 John BBB 1/22/1988
    2901 Carol CCC 3/30/1988
    11 Tim CCC 11/2/2010
    231 Larry CCC 9/2/2015
    221 Susan  DDD 9/2/2015

    Column 3 has some strings AAA AAA BBB CCC CCC CCC DDD. 

    They have been sorted alphabetically.

    What I want to do is find the range based on the string I am looking for in column 3. 

    For example, 

    If I am looking at string CCC 

    I would want to get range 5-7 (A5:D7) 

    I have tried :

     Excel.Range excelRange2 = excelWorksheet.Columns[3];
    
     excel1 = excelRange2.Find("CCC");
     firstRowNum = excel1.Row.ToString();

    To which I get row Number 5. But how do I find the last row where CCC has occurred. 

    I hope someone can help me with this. 

    Thank You in advance. 


    Wednesday, September 2, 2015 7:45 PM

Answers

  • Hi nmedelec,

    Based on my understanding, we can filter the data via Range.AutoFilter. Then we can check the filter data via the height property of range. The data doesn't meet the criteria will be hide.

    Here is an example that print the data in column A which column c equals "CCC":

    ActiveSheet.Range("$A$1:$D$8").AutoFilter Field:=3, Criteria1:="CCC"
      
    For Each aCell In ActiveSheet.Range("$A$1:$A$8")
        If aCell.Height <> 0 Then
            Debug.Print aCell
        End If
    Next aCell

    Data source:

    Result:

    Header1
     2901 
     11 
     231

    In addition, we can use Record Macro to get a sample code for the quick start.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by nmedelec Thursday, September 3, 2015 6:42 PM
    Thursday, September 3, 2015 2:59 AM

All replies

  • Hi nmedelec,

    Based on my understanding, we can filter the data via Range.AutoFilter. Then we can check the filter data via the height property of range. The data doesn't meet the criteria will be hide.

    Here is an example that print the data in column A which column c equals "CCC":

    ActiveSheet.Range("$A$1:$D$8").AutoFilter Field:=3, Criteria1:="CCC"
      
    For Each aCell In ActiveSheet.Range("$A$1:$A$8")
        If aCell.Height <> 0 Then
            Debug.Print aCell
        End If
    Next aCell

    Data source:

    Result:

    Header1
     2901 
     11 
     231

    In addition, we can use Record Macro to get a sample code for the quick start.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by nmedelec Thursday, September 3, 2015 6:42 PM
    Thursday, September 3, 2015 2:59 AM
  • This worked perfectly. 

    Thank You!!! 

    Thursday, September 3, 2015 6:42 PM