Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-05-2018, 10:53 PM
Josh1012 Josh1012 is offline How to Loop through rows and Columns to Offset Data Windows 10 How to Loop through rows and Columns to Offset Data Office 2010 64bit
Novice
How to Loop through rows and Columns to Offset Data
 
Join Date: Sep 2018
Posts: 12
Josh1012 is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 09-06-2018, 12:23 AM
ArviLaanemets ArviLaanemets is offline How to Loop through rows and Columns to Offset Data Windows 8 How to Loop through rows and Columns to Offset Data Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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!
Reply With Quote
  #3  
Old 09-06-2018, 01:31 AM
Josh1012 Josh1012 is offline How to Loop through rows and Columns to Offset Data Windows 10 How to Loop through rows and Columns to Offset Data Office 2010 64bit
Novice
How to Loop through rows and Columns to Offset Data
 
Join Date: Sep 2018
Posts: 12
Josh1012 is on a distinguished road
Default

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??
Attached Files
File Type: xlsx Example.xlsx (9.0 KB, 7 views)
Reply With Quote
  #4  
Old 09-06-2018, 01:43 AM
ArviLaanemets ArviLaanemets is offline How to Loop through rows and Columns to Offset Data Windows 8 How to Loop through rows and Columns to Offset Data Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

What about this?
Attached Files
File Type: xlsx YourExample.xlsx (9.6 KB, 9 views)
Reply With Quote
  #5  
Old 09-06-2018, 03:20 AM
Josh1012 Josh1012 is offline How to Loop through rows and Columns to Offset Data Windows 10 How to Loop through rows and Columns to Offset Data Office 2010 64bit
Novice
How to Loop through rows and Columns to Offset Data
 
Join Date: Sep 2018
Posts: 12
Josh1012 is on a distinguished road
Default That did the trick

Hey ArviLaanemets,



That did the trick and I just wrote a basic macro to automate it.



Thanks for the help
Reply With Quote
Reply

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
How to Loop through rows and Columns to Offset Data I cant get my Sum(Offset formula to pull appropriate data. Erickd90 Excel 7 07-28-2016 12:20 PM
How to Loop through rows and Columns to Offset Data Delete blank rows between the two rows that contain data beginner Excel Programming 5 12-26-2014 12:29 AM
How to Loop through rows and Columns to Offset Data Data validation list filter with range defined by OFFSET Mango123 Excel 4 03-18-2014 02:52 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:02 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft