Select current region ignoring cells in dropdown list

[Fractalis]

Hello to all,

I have a sheet with a data in range A1:J9. I have a drop down list in each cell from A2:A100.

I'm trying to store all values in A1:J9 in an array using the property CurrentRegion.value like below

Arr=Range("A1").CurrentRegion.Value

The issue is that is taking as current region the range A1:J100, even if I don't have selected any value
in the drop down list from A10:A100.

Is there a way to force CurrentRegion to select only values from A1:J9 ignoring the blanks dropdown list values
or a similar way to load an array with values in a contiguous range?


Thanks in advance for any help.

 

[Jerry Sullivan]

Hi Fractalis,

If you literally only need to read the values from A1:J9, you could just do this...

Arr=Range("A1:J9").Value

Assuming that what you mean is that you only want to read the cells that have values selected in Column A which might be A1:J8 or A1:J28 then here are some other things to check.

If your cells with the dropdowns are actually blank, then they shouldn't directly affect the size of the CurrentRegion.
If they appear blank but have space characters, then would directly affect the CurrentRegion.

Another possibility is that you have formulas in columns B:J that display blanks when A is blank and values when A is non-blank.
These cells with formula will be included as part of the CurrentRegion regardless of whether the formulas display blanks.

If this is the case, then an alternative would be to use the Range.Find method to find the last row with data in Columns A:J.

 

[Fractalis]

Hello Jerry,

Thanks for your answer.

It seems is what you said, the cells dropdown lists look blank but they have spaces and even with
code below the CurrentRegion is taking a range bigger that I would like.

ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Select

or

Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible, xlTextValues).Select

With the Range.Find how can I look the last row with data, ignoring those dropdown list cells that look
blank?

My goal is to load in an array the content of the table.

Thanks in advance for any help.

Regards

 

[Jerry Sullivan]

You could try the function below which finds the last cell in a one-column array that has data excluding blanks or spaces.

Sub PopulateArray()
   Dim lLastDataRow As Long
   Dim rLastNonBlank As Range
   Dim Arr As Variant
   
   '--get last row in column A with any data
   lLastDataRow = Cells(Rows.Count, "A").End(xlUp).Row
     
   '--check from lLastDataRow upwards for last cell
   Set rLastNonBlank = rGetLastNonBlankCell( _
      rOneColumnRange:=Range("A1:A" & CStr(lLastDataRow)))
   
   If rLastNonBlank Is Nothing Then
      Debug.Print "No non-blank cells in Column A"
   Else
      Arr = Range("A1:J" & rLastNonBlank.Row).Value
      Debug.Print "Read in: " & Range("A1:J" & rLastNonBlank.Row).Address
   End If
End Sub

Private Function rGetLastNonBlankCell( _
      rOneColumnRange As Range) As Range
'--returns last cell in range that is not blank or spaces.
'  returns Nothing if all cells are blank or spaces.

   Dim lNdx As Long
   Dim vArray As Variant
   
   If rOneColumnRange.Columns.Count = 1 Then
      If rOneColumnRange.Rows.Count = 1 Then
         If Trim(rOneColumnRange.Value) <> vbNullString Then
            Set rGetLastNonBlankCell = rOneColumnRange
            Exit Function
         End If
      Else '--more than one cell
         vArray = rOneColumnRange.Value
      
         For lNdx = UBound(vArray, 1) To 1 Step -1
         '--loop from last row up until first value found
         '     other than blanks or spaces
            If Trim(CStr(vArray(lNdx, 1))) <> vbNullString Then
               Set rGetLastNonBlankCell = Application.Index( _
                  rOneColumnRange, lNdx)
               Exit Function
            End If
         Next lNdx
      End If
   End If
   Set rGetLastNonBlankCell = Nothing
End Function

 

[Fractalis]

Hello Jerry,

Many thanks for your solution. I've tried and it works just fine, just one issue found in the file, that is that there are "blanks" dropdown cells
to the right of the table and is taking correctly the row with last nonblank cell, but is not still taking the correct column for last nonblank.

Your code detects as last row with data row 32, and the function selects correctly 14 as the row with no blank.

With line below I was able to match the same row with data (32)

LastRow=ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

but the last column of table with no blank is "D" and the code below gives me column "H"

LastCol=ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column

I'm trying to understand your function but I'm still not able to modify it in order to find the real last column
with no blank.

Could you help me please one more time.

Thanks again for such help so far.

 

[Jerry Sullivan]

Ah...I misinterpreted that Column J was the rightmost column of a fixed width data set range and that only the number of rows was variable.

The challenge of finding the last "blank" column is the same as it is with the rows. Since these cells have space characters as values, there isn't an easy way to find the rightmost cell excluding the ones with spaces. For true blanks, there are several methods like the ones you've tried.

I can modify the code to find the last blank column. Does Row1 always have the last column of displayed data (similar to the way Column A always has the last row of displayed data)?

 

[Fractalis]

Hello Jerry,

Row1 doesn't have always the last column of displayed data. Even though, not always column A has the last row.
I think could be useful to know the last row/column with blank data the specialCells and from that result, apply
your function to match only the range with visible data. You know much more than me, it's just a idea .

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column

Thanks for your help Jerry.

 

[Jerry Sullivan]

Here's a modified version for you to try.

Sub PopulateArray()
   Dim arr As Variant
   Dim rDisplayedRegion As Range
   
   Set rDisplayedRegion = GetDisplayedRegion( _
      rInput:=Range("A1"))
   arr = rDisplayedRegion.Value
   Debug.Print "Read in: " & rDisplayedRegion.Address

End Sub

Private Function GetDisplayedRegion(ByVal rInput As Range)
'--Returns range resulting from taking currentRegion of rInput and
'  removing "displayed blank" rows and columns from bottom and right side.
'  "displayed blank" cells are blank or consist of only space characters.

   Dim lLastRow As Long, lLastCol As Long
   Dim rFound As Range, rRegionAll As Range
   Dim sFirstAddr As String
   
   '--get region prior to considering blanks
   Set rRegionAll = rInput.CurrentRegion
   
   '--find last row without displayed blanks
   Set rFound = rRegionAll.Find(What:="*", After:=rRegionAll.Cells(1), _
      LookAt:=xlPart, LookIn:=xlValues, SearchOrder:=xlByRows, _
      SearchDirection:=xlPrevious, SearchFormat:=False)
   
   If rFound Is Nothing Then
      '--no values in rInput.CurrentRegion
      Set GetDisplayedRegion = rInput.Cells(1)
      Exit Function
   End If

   '--will loop until first non-blank found or return to first cell with value
   Do Until rFound.Address = sFirstAddr
      If Trim(CStr(rFound.Value)) <> vbNullString Then
         '--last row found
         lLastRow = rFound.Row
         Exit Do
      Else
         If sFirstAddr = vbNullString Then sFirstAddr = rFound.Address
         Set rFound = rRegionAll.Cells.FindPrevious(After:=rFound)
      End If
   Loop
   
   '--if no non-blank was found, return top left cell of rInput
   If lLastRow = 0 Then 'no non-blank found
      Set GetDisplayedRegion = rInput.Cells(1)
      Exit Function
   End If
      
   '--find last column in region remaining after excluding blank rows
   With rRegionAll.Resize(lLastRow - rRegionAll.Row + 1)
      Set rFound = .Find(What:="*", After:=.Cells(1), _
         LookAt:=xlPart, LookIn:=xlValues, SearchOrder:=xlByColumns, _
         SearchDirection:=xlPrevious, SearchFormat:=False)
      
      '--will loop until first non-blank found
      sFirstAddr = vbNullString
      Do Until rFound.Address = sFirstAddr
         If Trim(CStr(rFound.Value)) <> vbNullString Then
            '--last column found
            lLastCol = rFound.Column
            Exit Do
         Else
            If sFirstAddr = vbNullString Then sFirstAddr = rFound.Address
            Set rFound = .Cells.FindPrevious(After:=rFound)
         End If
      Loop
   End With
   
   '--return resulting range
   Set GetDisplayedRegion = rRegionAll.Resize( _
      lLastRow - rRegionAll.Row + 1, lLastCol - rRegionAll.Column + 1)
End Function

 

[Fractalis]

Hello Jerry,

Thanks so much. I've tried and it works so fine, detecting the actual range with visible data.

I'll study and test more slowly your code since I see there are several things I'm not too familiarized
like defined functions, use of find method in that short way, etc.

Many thanks again, I've learned and will learn more with your solution.

Best regards

 

[Jerry Sullivan]

Glad to hear that worked. Just ask if you have any questions while studying the code.