Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# moving data across formula

Posted on 2016-08-26
Medium Priority
37 Views

In some cases, address 1 is empty, and the application has for some reason stared the addresses in address 2. What I need is a formula to basically say - if column A is blank, move everything from column B-F over a cell so all records start in column A. If column A is not blank, take no action.
0
Question by:pma111
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2
• +2

LVL 52

Expert Comment

ID: 41771353
HI,

You will probably  need vba
pls send example

Regards
0

LVL 33

Expert Comment

ID: 41771356
Where  is the data coming from? Is it an import from text file or csv.

I suspect if its an import the first column contains a space and the import is recognising it as a value.

Thanks
Rob H
0

LVL 3

Author Comment

ID: 41771363
Hi

it was an extract of data from a 3rd party system, in *.csv format, but that is genuinely how its stored in the database it was extracted from.
0

LVL 32

Assisted Solution

Subodh Tiwari (Neeraj) earned 1336 total points
ID: 41771365
You may try something like this....

``````Sub DeleteBlankCells()
Dim lr As Long, i As Long
lr = ActiveSheet.UsedRange.Rows.Count
For i = 1 To lr
If Cells(i, 1) = "" Then
Range(Cells(i, 2), Cells(i, 5)).Cut Cells(i, 1)
End If
Next i
End Sub
``````
0

LVL 3

Author Comment

ID: 41771392
Sample data:

a      a      a      a      1
a      a      a      a      1
a      a      a      a      1
a      a      a      a      1
0

LVL 52

Assisted Solution

Rgonzo1971 earned 664 total points
ID: 41771396
Hi,

pls try

``````Sub DeleteBlankCells()
Set Rng = ActiveSheet.UsedRange.Resize(, 1).SpecialCells(xlCellTypeBlanks)
Rng.Delete shift:=xlShiftToLeft
End Sub
``````
Regards
0

LVL 32

Accepted Solution

Subodh Tiwari (Neeraj) earned 1336 total points
ID: 41771405
Why not only this? :)

``````Sub DeleteBlankCells()
On Error Resume Next
ActiveSheet.UsedRange.Resize(, 1).SpecialCells(xlCellTypeBlanks).Delete xlToLeft
End Sub
``````

Error handling is required because if there are no blank cells in col. A or the code is run twice, the code will throw an error.
0

LVL 3

Author Comment

ID: 41810511
I have been off sick for a while so didnt have chance to split the points... happy with the suggestion or if you reopen it I can do it instead
0

LVL 14

Expert Comment

ID: 41815707
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Subodh Tiwari (Neeraj) (https:#a41771405)
-- Rgonzo1971 (https:#a41771396)
-- Subodh Tiwari (Neeraj) (https:#a41771365)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk
Experts-Exchange Cleanup Volunteer
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month5 days, 19 hours left to enroll