PDA

View Full Version : [SOLVED] Dynamic Range to accommodate data



esherr77
06-07-2017, 06:10 PM
Hi there fellas
I have to pull some information from a site, then I have to re arrange it as per screenshots.
I have wrote some code, which get's it done but I do believe this code is very generic and not eficcient ( I mean, I have typed the code several times in order to make it work for me, but was wondering if anybody has a suggestion to improve it )?
194221942319421

mdmackillop
06-08-2017, 02:34 AM
Please post a workbook; not screenshots

SamT
06-08-2017, 04:12 AM
This might work. Replace just the code in your Macro with this code

Dim ScheduleArray As Variant
Dim i As Long
Dim Rw As Long 'Rw=0 ATT

'Application.ScreenUpdating = False 'Uncomment after testing

With ActiveSheet
With Range("A1").CurrentRegion
ScheduleArray = .Value
.ClearContents
End With

For i = LBound(ScheduleArray) To UBound(ScheduleArray) Step 3
Rw = Rw + 1
With .Rows(Rw)
.Cells(1) = ScheduleArray(i, 1)
.Cells(2) = ScheduleArray(i, 2)
.Cells(3) = ScheduleArray(i + 1, 2)
.Cells(4) = ScheduleArray(i + 2, 1)
.Cells(5) = ScheduleArray(i + 2, 2)
End With
Next i

End With 'ActiveSheet

Application.ScreenUpdating = True
End Sub

esherr77
06-22-2017, 08:09 PM
Hey it actually did worked like a charm, thank you very much.
Yet I am not certain how it does it!

SamT
06-23-2017, 06:37 AM
Google "VBA Excel CurrentRegion"
Schedulearray is an array identical to that CurrentRegion, but just its values.

The two numerical values in ScheduleArray(n, n) Represent "rows" and "columns." Note that "i" is incrementing by 3 (Step 3) as it counts down the "rows" of the array.

Note that An Array's "rows" and "columns" can sometimes be transposed from the Range's Rows and Columns, but not in this particular case.

So, for each group of three rows and two columns, choose which ones go in which Cell in that Range Row (Rw)

For any given Range, Excel VBA counts Cells from Left to Right, then down. So... Row(2).Cells(3) would be Range("C2")