Copy Data to Another Cell Based on Criteria

Dear Experts,

I have a .CSV file emailed to me each morning which I import into a Excel spreadsheet for analysis.  Since the application creating the file has been upgraded the formatting of the file has gone a bit wobbly.

For the some rows, data has blank cells between it and the next set of data.

I am trying to write a macro which look at the data and cut and paste data to another cell if it encounters a blank cell.  Once this criteria is no longer met, end the macro run.

Very (very) basic if statement I had written as a test to get all of the data into columns A:E:

If Range("C11") = "" Then
   Range("E11").Select
   Selection.Cut
   Range("C11").Select
   ActiveSheet.Paste

Else
MsgBox "Not blank"

End If

Attached is an example of formatting I am struggling with.

Any help you could give me would be greatly appreciated.

Regards,
Infosec36
raw_data.xlsx
Sonia BowditchInformation Security OfficerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this....

Please notice that the code assumes row11 as the actual header row for your data. Change it as per your requirement.

Sub RemoveAllBlankCells()
Dim lr As Long, lc As Long
lr = cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lc = cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Range(cells(11, 1), cells(lr, lc)).SpecialCells(xlCellTypeBlanks).Delete shift:=xlToLeft
End Sub

Open in new window


Does this help?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sonia BowditchInformation Security OfficerAuthor Commented:
Perfect.  Thank you.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I could help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.