Results 1 to 13 of 13

Keep or store rows for each non-repeated name

Threaded View

  1. #1
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Keep or store rows for each non-repeated name

    Hello excel experts

    In the first worksheet, there are values from range("A2:F" & lr) where lr is the last row based on column A. In column D, there are names (the names are repeated). I need to build a dictionary for the unique names (column D) which will be keyed. Each key should store a 2d array of the rows related to that key.

    I found that code but I got errors on this line [ReDim Preserve rows(LBound(rows) To UBound(rows) + 1, 1 To 6)]
    Sub BuildDictionary()
    
        Dim dict As Object
        Set dict = CreateObject("Scripting.Dictionary")
        
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets(1)
        
        Dim lr As Long
        lr = ws.Cells(ws.rows.Count, "A").End(xlUp).Row
        
        Dim data As Variant
        data = ws.Range("A2:F" & lr).Value
        
        Dim i As Long
        For i = 1 To UBound(data)
            Dim key As String
            key = data(i, 4)
            If Not dict.Exists(key) Then
                dict.Add key, Array()
            End If
            Dim rows As Variant
            rows = dict(key)
            ReDim Preserve rows(LBound(rows) To UBound(rows) + 1, 1 To 6)
            rows(UBound(rows), 1) = data(i, 1)
            rows(UBound(rows), 2) = data(i, 2)
            rows(UBound(rows), 3) = data(i, 3)
            rows(UBound(rows), 4) = data(i, 4)
            rows(UBound(rows), 5) = data(i, 5)
            rows(UBound(rows), 6) = data(i, 6)
            dict(key) = rows
        Next i
    
    End Sub
    Last edited by KingTamo; 03-11-2023 at 05:47 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help! sum repeated rows
    By SHAYJOSH in forum Excel General
    Replies: 4
    Last Post: 07-22-2018, 04:44 PM
  2. Rows to be repeated at the bottom
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2015, 04:48 PM
  3. [SOLVED] Moving data from Rows to columns and delete repeated rows
    By mikearmanios in forum Excel General
    Replies: 5
    Last Post: 04-20-2014, 06:25 AM
  4. Macro to store results of repeated calculation
    By universal-E in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-26-2012, 10:16 AM
  5. Remove repeated rows.
    By Hugo555 in forum Excel General
    Replies: 1
    Last Post: 05-07-2009, 03:09 AM
  6. sum once for repeated rows
    By Jasonic in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-11-2009, 12:53 PM
  7. Deleting repeated rows
    By anjanesh in forum Excel General
    Replies: 3
    Last Post: 06-03-2006, 12:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1