Divide Range in smaller Ranges based on Cell-Values

BuRnZz

New Member
Joined
Dec 9, 2020
Messages
27
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hey MR. Excel Members,
I know the title sucks but let me explain as I think this is fairly easy but I cant manage to solve it.
I have a table that looks like this:

00f976cbab45513430e67d5ac37b101c.png


Now I want to loop through this table and set a range named "xvalues" to the B-column values for each platform (entry in column A) so that I can then put those values into a chart.
Basically the output should look something like this:

1st loop: Xvalues = Range("B18:B21") 'ChromeVersion76 values
2nd loop: Xvalues = Range("B22:B25") 'EdgeVersion 18 Values
...
4th loop: Xvalues = Range("B30:B33") 'Firefox69 Values


What Im trying so far is building sth like this but Im just very stuck:

VBA Code:
Dim TableSection As Range
    Set TableSection = FunctionToSetTableSection '(Sets TableSection to Range A18-A33)
    TableSection.Rows.Count
    RemoveDuplicates (RoteReiheRowValue)
    
    
    
    Dim cell As Range
    Dim EmptyCellCounter As Long
    EmptyCellCounter = 1
    Dim StartReihe As Long
    Dim EndReihe As Long

    
    For Each cell In TabellenAbschnitt
        If cell.Value <> "" Then
            Reihenzähler = Reihenzähler + 1
        Else


As the table can vary in length and number of plattform-entries i wanted to somehow count how long each section is and then make the selection but cant get it to work. Im new to VBA and just very frustrated since hours.

ANY help on this would be greatly appreciated!!!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Herakles

Board Regular
Joined
Jul 5, 2020
Messages
73
Office Version
  1. 365
Platform
  1. Windows
Hi.

It would be easier if the Platform column was fully populated so that there were no gaps.
Is that possible?

Do you have a list of possible entries in the Platform column or will they always be as shown and nothing else?
 

BuRnZz

New Member
Joined
Dec 9, 2020
Messages
27
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hi.

It would be easier if the Platform column was fully populated so that there were no gaps.
Is that possible?

Do you have a list of possible entries in the Platform column or will they always be as shown and nothing else?
No, its usually completely filled but I thought it would be easier like this. It woould all be the same entry so
A18-A22 Values would be "Chrome Version 76.0"
A22-A25 Values would be "Edge Version 18" and so on... so all filled, hope that helps :)
And THANK YOU so much for an answer :))
 

Herakles

Board Regular
Joined
Jul 5, 2020
Messages
73
Office Version
  1. 365
Platform
  1. Windows
I have written a function which will return a range based upon the worksheet name, table name and value in the Plattform column that
you are looking for.

You will need to change the values highlighted as appropriate.

I cannot take it any further as I do not know how you are to use the range returned.

I hope that this helps or just gives you an idea.

VBA Code:
Private Sub subCallingProcedure()
Dim rng As Range
Dim i As Integer
Dim Ws As Worksheet

    Set Ws = Worksheets("[B]Sheet1[/B]")
    
    Set rng = fncGetRange(Ws, "[B]TabellenAbschnitt[/B]", "[B]Plattform Name[/B]")
    
    MsgBox rng.Address
    
End Sub

Public Function fncGetRange(Ws As Worksheet, strTableName As String, strPlattform As String) As Range
Dim tbl As ListObject
Dim rng As Range
Dim rngFound As Range
Dim intCount As Integer

    Set tbl = Ws.ListObjects(strTableName)
    
    Set rng = tbl.ListColumns(1).DataBodyRange
        
    With rng
        Set rngFound = .Cells.Find(what:=strPlattform, _
            after:=.Cells(.Cells.Count), LookIn:=xlValues, _
            lookat:=xlWhole, searchorder:=xlByRows, _
            searchdirection:=xlNext, MatchCase:=False)
    End With
            
    intCount = Application.WorksheetFunction.CountIf(rng, strPlattform)
   
    Set fncGetRange = rngFound.Resize(intCount, 1).Offset(0, 1)

End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,129,777
Messages
5,638,275
Members
417,019
Latest member
PKDP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top