Inserting rows in range based on cell value within a loop

PWild

New Member
Joined
Mar 2, 2020
Messages
2
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Hello all. I am trying to create a tool that will improve my life by importing, massaging and exporting data in the form I need. So far I have a separate submodule that imports all the data into excel, does some filtering, then combines the data side by side on one sheet.

Heres the issue. Each batch of data is only useful when compared at the same time, and due to the simplicity of the software downstream the output data must be presented on a single data sheet. My goal is to shift the data (not column headers) down so the times line up with the most important reference logger so I can do further manipulations and then export the info (ex: if logger_2 starts 15 minutes after logger_refrence then I would like the range of data for logger_2 to move down 15 rows).

I have already created a cell that will determine the number of rows the data needs to be moved down, I am running issues into moving the data down that many rows while also using a for loop so I can change the number of loggers used in the future.

Columns.PNG


All data is imported in n row by 6 column chunks and placed directly adjacent to each other (Columns A:F are logger 1, G:L is logger 2, etc). Cell with the value to move down is C1, I1, etc and is calculated in a different submodule. Columns start row 2 and if at all possible I'd like to leave them alone, just offset the data range.

I apologize for my poor code, I took a vba course in college and never looked back (and it shows).

Cheers!


Sub DownData()

'Start counter
Dim i As Integer
Dim k As Double

Dim LastCell As Range, RowLength As Long
Dim NumberImported As Integer

'Check Number of Columns
With Range("A2").EntireRow
Set LastCell = .Cells(1, .Columns.Count).End(xlToLeft)
End With

'Determine Length of Row2, determine number of imported CSV files
RowLength = LastCell.Column
NumberImported = (RowLength / 6)
'Range("D1") = NumberImported

'Re-set position/worksheet before running
ActiveSheet.Range("A1").Select
Worksheets("Combined_Data").Activate

'Count range. 1 to total minus one so first slot skips the sensor, then goes to sensor-1
For i = 1 To (NumberImported - 1)

'Position of the cell w/ number needed to move down <----Error occurs here
k = Range(1, (3 + (i * 6))).Value
'Range of the cells, from 3rd row per logger input (six * number in)
Range(Cells(3, (i * 6) + 1), Cells((k + 2), (i * 6) + 6)).Insert

Next

End Sub
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

PWild

New Member
Joined
Mar 2, 2020
Messages
2
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Please ignore; changed k = Range(1, (3 + (i * 6))).Value to k = Cells(1, (3 + (i * 6))) and everything worked like a charm.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,711
Messages
5,626,427
Members
416,183
Latest member
IanA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top