Sorry I am not sure what it is I am doing wrong but I can't get this to work
Posts by Colin7


So can I just name these cells say "Description" then add For each r in range(Description)?

Would it be possible to put these cells in a named range then refer to the named range in the code to make it dynamic?

I tried running this code I had to cancel it as it was running for near 10 minutes and still never finished. I dont need to do the whole of column C just them individual cells.
Thanks

graha_karya,
Thanks for your reply. Is there a way to use a named range instead of listing every cell?

Hi,
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
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[/VBA]Thanks in advance for any help with this!

graha_karya,
Thanks for your help on this. Your code is brilliant

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
etc 
How does this work?
on sheet 1 cell C23 I need the formula =test("GSA",sheet2,E16:E416)
on sheet 1 cell C13 I need the formula =test("GSA",sheet2,E520:E920)
on sheet 1 cell C33 I need the formula =test("GSA",sheet2,E1100:E1500)
and so on

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 = GSA700
E17 = BLANK CELL
E18= BLANK CELL
E19 = GSA702
E20= GSA700A
and so onMy 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,
GSA700, GSA700A, GSA702.
Hope this helps and thank you again for anything you can do to solve this problem

Just one thing. The "ARD" reference was only an example. Each time I run this code the reference will be different for example it could be GRSD9005, GRSD9006, GRSD9007a etc or A501, A502 A504a, A504b, A505. Could this code be fixed to allow for this?
Thank you

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 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 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.

KjBox can this be done or is it not possible?

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.

Hi,
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
Thanks