#1
|
|||
|
|||
How to Loop through rows and Columns to Offset Data
Hi,
I would like to know to how to Loop through all rows that have data and offset them by one column (in the the negative direction) and also select cells in all columns that have data in them and offset them to the same column as the previous data. So this is the code I currently have written myself: Sub Offset() Dim Col As Long Dim Row As Long Dim myrg As Range Dim newrow As Integer Dim newcol As Integer Col = Application.InputBox(Prompt:="Select Column your working in",Title:="Column", Type:=1) newcol = Col - 1 Row = 1 newrow = Row + 1 For Row = 1 To 500 For Col = Col To Col + 1 If Cells(Row, Col) > 0 Then Cells(Row, Col).Offset(, newcol).Select ElseIf Cells(Row, Col) = "" Then GoTo newrow Next Row Next Col End If End Sub I used an input box so I can tell excel what column I want to offset first. I want to add a conditional statement to say if there is no data in the cell then to just skip to the next row. |
#2
|
|||
|
|||
Why not simply delete a column at left before your datarange? When you move all data to left a cell, all previous entries in this column are overwritten anyway!
|
#3
|
|||
|
|||
Hi,
Thanks for the prompt reply. I simply can't delete a column on the left as I am compiling data from left to right and I'm copying different range of cells that are staggered for example I have attached a spreadsheet as a very basic example of what I want to do, but I obviously will have many rows. So essentially I want to re-align all the data (In this case ID numbers in Example.xlsx) in a single column without having to do it manually using the offset feature. Unless you have a better alternative?? |
#4
|
|||
|
|||
What about this?
|
#5
|
|||
|
|||
That did the trick
Hey ArviLaanemets,
That did the trick and I just wrote a basic macro to automate it. Thanks for the help |
Tags |
for each loop, offset(), vba code |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Insert rows when column A data changes then shade and autosum blanks in columns E to I | kgoosen | Excel Programming | 0 | 08-30-2017 04:45 AM |
Formatting data in scattered rows into rows following each other | MorneDJ | Excel | 3 | 12-10-2016 12:35 AM |
I cant get my Sum(Offset formula to pull appropriate data. | Erickd90 | Excel | 7 | 07-28-2016 12:20 PM |
Delete blank rows between the two rows that contain data | beginner | Excel Programming | 5 | 12-26-2014 12:29 AM |
Data validation list filter with range defined by OFFSET | Mango123 | Excel | 4 | 03-18-2014 02:52 PM |