Posts by Colin7


    I have this code to fit row heights in merged cells. This works ok but it takes a few minutes to run. I also have the problem of when I insert a new row or delete a row these cell references are then no longer correct. Can anyone adjust this formula to speed it up and use maybe a named range to reference these cells so they adjust when rows are inserted and deleted before the code is run?

    [VBA]Sub Fit_Row_Heights()
    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("C14", "C61", "C108", "C155", "C202", "C249", "C296", "C343", "C390", "C437", "C534", "C484", "C531", "C578", "C625", "C672", "C719", "C766", "C813", "C860", "C907", "C954", "C1001", "C1048", _
    "C1095", "C1142", "C1189", "C1236", "C1283", "C1330", "C1377", "C1424", "C1471", "C1518", "C1565", "C1612", "C1659", "C1706", "C1753", "C1800", "C1847", "C1894", "C1941", "C1988", "C2035", "C2082", "C2129", _
    "C2176", "C2223", "C2270", "C2317", "C2364", "C2411", "C2458", "C2505", "C2552", "C2599", "C2646", "C2693", "C2740", "C2787", "C2834", "C2881", "C2928", "C2975", "C3022", "C3069", "C3116", "C3163", "C3210", _
    "C3257", "C3304", "C3351", "C3398", "C3445", "C3492", "C3539", "C3586", "C3633", "C3680", "C3727", "C3774", "C3821", "C3868", "C3915", "C3962", "C4009", "C4056", "C4103", "C4150", "C4197", "C4244", "C4291", _
    "C4338", "C4385", "C4432", "C4479", "C4526", "C4573", "C4620", "C4667", "C4714", "C4761", "C4808", "C4855", "C4902", "C4949", "C4996", "C5043", "C5090", "C5137", "C5184", "C5231", "C5278", "C5325", "C5372", _
    "C5419", "C5466", "C5513", "C5560", "C5607", "C5654", "C5701", "C5748", "C5795", "C5842", "C5889", "C5936", "C5983", "C6030", "C6077", "C6124", "C6171", "C6218", "C6265", "C6312", "C6359", "C6406", "C6453", _
    "C6500", "C6547", "C6594", "C6641", "C6688", "C6735", "C6782", "C6829", "C6876", "C6923", "C6970", "C7017", "C7064", "C7111", "C7158", "C7205", "C7252", "C7299", "C7346", "C7393", "C7440", "C7487", "C7534", _
    "C7581", "C7628", "C7675", "C7722", "C7769", "C7816", "C7863", "C7910", "C7957", "C8004", "C8051", "C8098", "C8145", "C8192", "C8239", "C8286", "C8333", "C8380", "C8427", "C8474", "C8521", "C8568", "C8615", _
    "C8662", "C8709", "C8756", "C8803", "C8850", "C8897", "C8944", "C8991", "C9038", "C9085", "C9132", "C9179", "C9226", "C9273", "C9320")
    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
    mw = mw + Rng.Cells.Count * 0.66
    .Cells(1).ColumnWidth = mw
    rwht = .RowHeight
    .Cells(1).ColumnWidth = cw
    .MergeCells = True
    .RowHeight = rwht
    End With
    Next i
    Application.ScreenUpdating = True
    End Sub[/VBA]

    Thanks in advance for any help with this!

    yes your code works perfectly. I was just wondering if it could be adjusted so I did not have to keep retyping the letters into the formulas everytime they changed. Could the code not just order the numbers and disregard the letters in the string?

    job 1 the letters could be GSA-
    job 2 the letters could be ARD-
    job 3 the letters could be TRSQ

    My only problem is that there are 20 of these formulas on one sheet which pull numbers from different ranges. So I would have to type in the letters to 20 different formulas each time. Is there a way to do this that isn't a UDF and it would automatically put the numbers in numerical order regardless of which letters there are?

    Thank you

    Amazing, thanks very much for this. This is perfect but is there anyway it could run without having to type in the letters each time as I do a lot of these everyday? Also I use a UDF to concatenate the range E16:E416 on another sheet removing blank cells. Could you put this into your code aswell?

    Sorry for being a nuisance.

    Thank you

    Also I am originally pulling these numbers from a range on another sheet and combining them into a string removing blanks. For example Sheet 2 range E16:E416 will be like so:

    E16 = GSA-700
    E17 = BLANK CELL
    E19 = GSA-702
    E20= GSA-700A
    and so on

    My overall goal is to combine these cells into a string remove blanks and duplicates and putting in numerical order separated by a comma as below,

    GSA-700, GSA-700A, GSA-702.

    Hope this helps and thank you again for anything you can do to solve this problem

    Thanks for your reply

    I will have 3 different possibilities

    - Numbers, example 95, 100, 101, 101, 103, 103b, 102, 103c, 104, 102 which should be ordered 95, 100, 101, 102, 103, 103b 103c, 104. Numbers here will always be at the start and some with letters after. Remove duplicates.

    - Letters and number, example ARD100, ARD101, ARD101b, ARD100, ARD102, ARD95, ARD107a which should be ordered ARD95, ARD100, ARD101, ARD101b, ARD102, ARD107a. Letters at the start will be the same followed by numbers and some with letters after. Remove duplicates

    - Letters and number separated, example ARD-100, ARD-101, ARD-101b, ARD-100, ARD-102, ARD-95, ARD-107a which should be ordered ARD-95, ARD-100, ARD-101, ARD-101b, ARD-102, ARD-107a. Letters at the start will be the same followed by a dash then numbers and some with letters after. Remove duplicates.

    Hope this helps and is clear. If you could achieve this in one code that would be brilliant but maybe it needs a few separate codes which I could run depending on the type of values?

    Thanks again for your help, much appreciated.

    Thanks for your reply's, greatly appreciated. Your codes work perfectly but another thing I should've mentioned is sometimes the values will be numbers only and other times it will have have numbers and letters mixed for example A100, A101, A105, A104, A102, A87, A101. How could i adjust your code so the outcome will be A87, A100, A101, A102, A104, A105. I need it to work with both numbers only and numbers and letters. If there is a way to do this I would be extremely grateful. Sorry I never mentioned this before.


    I have numbers in a single cell for example 10, 3, 5, 3, 45, 12, 16, 105, 107, 12

    I would like a VBA code to sort these numbers so they will look like so 3, 5, 10, 12, 16, 45, 105, 107. Removing duplicates and sorting in order.

    Any help on tjis would be greatly appreciated as I can not find any solutions on the internet