Results 1 to 3 of 3

Thread: Creating an Array with a Range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Creating an Array with a Range

    Hey,

    I need help with creating an array from a range of cells


    I currently have this code in place:

    Option Explicit
    Option Base 1
    Sub FixMerge()
    Dim mw As Single
    Dim cM As Range
    Dim rng As Range
    Dim cw As Double
    Dim rwht As Double
    Dim ar As Variant
    Dim i As Integer
    Application.ScreenUpdating= False
    ar = Array("E80", "E81", "E82", "E83","E84", "E85", "E86", "E87", "E88", "E89", "E90")
    For i = 1 To UBound(ar)
    On Error Resume Next
    Set rng =Range(Range(ar(i)).MergeArea.Address)
    With rng
    .MergeCells = False
    cw = .Cells(1).ColumnWidth
    mw = 0
    For Each cM In rng
    cM.WrapText = True
    mw = cM.ColumnWidth + mw
    Next
    mw = mw + rng.Cells.Count * 0.66
    .Cells(1).ColumnWidth = mw
    .EntireRow.AutoFit
    rwht = .RowHeight
    .Cells(1).ColumnWidth = cw
    .MergeCells = True
    .RowHeight = rwht
    End With
    Next i
    Application.ScreenUpdating = True
    End Sub


    (not allowing me to put this in code, sorry)
    As you can see, I individually listed each cell between E80 to E90. The code is working fine as is but I need to make the range much longer (E80:E220) however I’m having trouble properly inserting the range into the array. I tried:


    Dim myArr As Variant
    myArr = Range(“E80:E220”)

    I’ve search all over the internet and apparently that should be working, but it is not. Can somebody here please help me properly set this up? It's probably something so stupid that I am missing, but I'm new to this stuff and just can't seem to figure it out...

    Thank you
    Last edited by CAMARD2; Jan 22nd, 2019 at 11:20 AM.
    0 0
     

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,648
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Creating an Array with a Range

    0 0
     

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,149
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Creating an Array with a Range

    Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
    Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10
    0 0
     

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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