Posts by brin1980

I'm not sure what you're referring to as the formula i see is referenced from the start to the end of the data set.

Hi all,

I've a spreadsheet which has a medianif formula which use to work, and works occasionally but mostly only presents 0 value results and i'm not sure why.

Basically the formula is meant to report the median price if the number of bedrooms/bathrooms/car spaces match from the table. I've attached the spreadsheet for your reference.

As mentioned above, it works for some criterias but not all of them.

thanks,

Ernest

i'm using the code from post #8.

it takes about 20~30 mins, i've processed 3 files so far

I have around 100 files to process

Hey mate,

It worked.... there was special characters which was causing the error. It only took 20 mins too...

thank you so much for your help.

I did try code from #7 but it doesn't actually seems like it's does anything.... that said i only waited for a few minutes and then stopped it.

There is no difference between the actual workbook and the sample i gave you, only the number of rows.

the one you sent in post #8

and yes, i updated the code with you message from post #9

how do i find that out?

just tried it but it throw an error

Run-time error '13': Type mismatch

Hi Jolivanes,

I will try to explain it as best i can....

The code needs to go through "column I" and any instances where there is a row in "column I" that <> "" AND the next row does = "", THEN concatenate all rows in column J, starting from the first blank row in column I, down to the last blank row in column I. The concatenated values from column J are then inserted into "column K" on the same row where "Column I" <> "". All the rows where I = "" are then deleted and moved up. The code should then loop through the rest of the worksheet until the end of the data set.

Again i hope this makes sense.

Thanks for your help.

Hi Roy,

Thank you so much for responding and for your code.

I've taken it and had to loop it so that it goes through all the instances in the workbook. I'm sure that this is not the best way to do it given your commences on avoiding loops.

I've attached a file with an example of the data i'm trying to modify to give you a better idea of what i'm trying to achieve. Although your code seems to be quicker, it will still take a number of hours to go through 500K rows.

any further suggestions you might be happy to help me with?

again thanks for your help.

Hi all,

i'm very new to VBA and have created a macro that loops through concatenating values in a variable number of rows and then pastes the joined string into a cell and deletes all the rows where the values were concatenated from. I have a spreadsheet that has 500K rows and this macro takes a good 10 hours to process.

Just wondering how i could change the macro to dramatically reduce the running time.

Here is the macro:

Sub a()


Do While ActiveCell.End(xlDown) <> ""


Range("i1").Select
ActiveCell.End(xlDown).Select


Dim my_range As Range
Dim no_of_rows As Long
Dim overall_string As String
Dim i As Long


Set my_range = Sheets("Sheet").Range(ActiveCell.Offset(1, 1), ActiveCell.End(xlDown).Offset(-1, 1))
no_of_rows = my_range.Rows.Count


For i = 1 To no_of_rows
overall_string = overall_string & " - " & my_range(i, 1)
Next i


ActiveCell.Offset(0, 2) = overall_string


Dim my_range2 As Range
Dim overall_string2 As String


Set my_range2 = Sheets("Sheet").Range(ActiveCell.Offset(1, 3), ActiveCell.End(xlDown).Offset(-1, 3))
no_of_rows2 = my_range.Rows.Count


For i = 1 To no_of_rows2
overall_string2 = overall_string2 & " - " & my_range2(i, 1)
Next i


ActiveCell.Offset(0, 4) = overall_string2


Range(ActiveCell.Offset(1, -8), ActiveCell.End(xlDown).Offset(-1, 9)).Delete Shift:=xlUp
Loop


End Sub