VBA macro to Copy and Paste Selections of different length in a list

[suraj8900]

I have a spreadsheet with information I wan to copy in Columns C through to V. Column V has a number, then lots of blanks then a number, then lots more blanks, then a number...etc. I want to copy the selection from column C to V, starting at the row with a number in column V, down to the row 1 above the next number in column V. Then later want to paste a (different) selection into column W (i.e. 1 cell to the right of the number in column v). I then want to go down to the next row with a number in column V, and repeat the whole process until I reach blank cells at the bottom.

e.g
select range from C2:V10
copy and paste into sheet 4 cell C2
paste (different) selection (from clipboard) into W10
select range from C11:V15
copy and paste into sheet 4 cell C2
paste (different) selection into W11
....etc

Can anyone help me with this please? I am new to this My friend told me use the functions Range, Offset, Loop, Do, Until - but I have tried for ages and can't figure it out!

 

[suraj8900]

I figured it out. But can somebody please help me speed it up? It ran all night and only got 10% of the way through the sheet.

Sub Macro6()
'
' Macro6 Macro
'


'
   Range("C5697").Select
   Do
        a = ActiveCell.Row
        ActiveCell.Offset(0, 21).Select
        Do
            ActiveCell.Offset(1, 0).Select
            Value = ActiveCell.Value
        Loop While Value = ""
        ActiveCell.Offset(-1, 0).Select
        N = ActiveCell.Row - a
        Range(ActiveCell, ActiveCell.Offset(-N, -21)).Copy
        
        Sheets("Sheet4").Select
        Range("C2").Select
        ActiveSheet.Paste
        SolverOk SetCell:="$X$2", MaxMinVal:=2, ValueOf:="0", ByChange:="$S$2:$U$2"
        SolverSolve True
        Range("S2:U2").Select
        Selection.Copy
        Sheets("Sheet1").Select
        
        ActiveCell.Offset(-N, 1).Select
        
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        ActiveCell.Offset(N + 1, -22).Select
        Sheets("Sheet4").Select
        Cells.Select
        Application.CutCopyMode = False
        Selection.ClearContents
        Range("C2").Select
        Sheets("Sheet1").Select
    
    Loop Until Value = abc
    
End Sub

 

[mirabeau]

I figured it out. But can somebody please help me speed it up? It ran all night and only got 10% of the way through the sheet.

Hi,

It's not very clear to me just what you want to achieve, i.e. what is to be speeded up?

To make it a bit easier for readers and potential helpers, could you post an example of your initial data, and also what you want any result to look like?

 

[suraj8900]

Hi,

It's not very clear to me just what you want to achieve, i.e. what is to be speeded up?

To make it a bit easier for readers and potential helpers, could you post an example of your initial data, and also what you want any result to look like?

Hi. Thanks for your reply. Attached is the spreadsheet (I've deleted most the data from the bottom due to file size, but it goes on upto row 60,000)
I'm using solver to minimise the sum of squared differences, between the 2 yellow shaded columns (one is a number, other a function based on 3 input parameters), by changing the 3 parameters shaded in blue. I'm copying and pasting to a separate sheet and back to find the solution, as I found it ran faster there than in the main large sheet.

Have any tips for speeding up the whole process? I found that the part where solver is running and it says "setting up problem" and then "solution 1, solution 2,...etc" takes most of the time, so that each loop of the macro takes over 1 minute. At this rate, it will take 48 hours to finish! (the main outer loop needs to run about 3000 times to get through all the data)

https://www.dropbox.com/s/cplhmloghpiaogm/All Option Prices Combined1.xlsm