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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
Does this help?
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
Does this help?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial