VBA code to remove spaces from a cell, check cell value, and insert row, copy , paste based on condition

[rawjdog]

I don't know much about vb, trying to figure out some coding.
I managed to extract data from many pdfs into excel, couple 100 lines.
I need vb code to:

1. remove the spaces from the Report # column
2. remove the spaces from the Date column, and turn into a date recognized by excel
3. checks Units Involved column, if more than 1, then copy, insert, paste that line into a new line below.


(I used the .....'s to try to separate and line up the columns for display purposes only)

example of the starting data:

Report #....................Date..................Units Involved
2 0 1 4 0 0 1 1 5.........1 2 1 6 2 0 1 3.....0 2
2 0 1 4 0 0 1 1 8.........1 2 2 9 2 0 1 3.....0 1
2 0 1 4 0 0 1 2 0.........0 1 0 1 2 0 1 4.....0 3
2 0 1 4 0 0 1 2 2.........0 1 1 3 2 0 1 4.....0 4
2 0 1 4 0 0 1 2 4.........0 1 2 0 2 0 1 4.....0 2


how the finished data should look:

Report #..........Date..............Units Involved
201400115.......12/16/2013.....02
201400115.......12/16/2013.....02
201400118.......12/29/2013.....01
201400120.......01/01/2014.....03
201400120.......01/01/2014.....03
201400120.......01/01/2014.....03
201400122.......01/13/2014.....04
201400122.......01/13/2014.....04
201400122.......01/13/2014.....04
201400122.......01/13/2014.....04
201400124.......01/20/2014.....02
201400124.......01/20/2014.....02


thank you for any advice or suggestions!

 

[XLfriend777]

Hi rawjdog - To start with you might try the TRIM function (Which removes, leading, trailing and multiple spaces). If the first row of data is in row two, with Report # in A2, Date in B2 and Units Involved in C2, then you might put in columns cell E2 =TRIM(A2), cell F2 = TRIM(B2) and G2 =TRIM(C2). Then try formatting the new date column, G, as an excel date. You can then fill these formulas down. Hope this helps get you started.

 

[XLfriend777]

Hi again rawjdog - To convert the date column you might try in column F the equation -> =LEFT(TRIM(A2),2) & "/" & LEFT(RIGHT(TRIM(A2),6),2) &"/" & RIGHT(TRIM(A2),4)

That should TRIM and convert the cells to a date-like format. You can then apply excel formatting as Short Date.

 

[hiker95]

rawjdog,

Sample raw data in the active worksheet:

Excel 2007
	A	B	C
1	Report #	Date	Units Involved
2	2 0 1 4 0 0 1 1 5	1 2 1 6 2 0 1 3	0 2
3	2 0 1 4 0 0 1 1 8	1 2 2 9 2 0 1 3	0 1
4	2 0 1 4 0 0 1 2 0	0 1 0 1 2 0 1 4	0 3
5	2 0 1 4 0 0 1 2 2	0 1 1 3 2 0 1 4	0 4
6	2 0 1 4 0 0 1 2 4	0 1 2 0 2 0 1 4	0 2
7
8
9
10
11
12
13
14
Sheet1

After the macro:

Excel 2007
	A	B	C
1	Report #	Date	Units Involved
2	201400115	12/16/2013	02
3	201400115	12/16/2013	02
4	201400118	12/29/2013	01
5	201400120	01/01/2014	03
6	201400120	01/01/2014	03
7	201400120	01/01/2014	03
8	201400122	01/13/2014	04
9	201400122	01/13/2014	04
10	201400122	01/13/2014	04
11	201400122	01/13/2014	04
12	201400124	01/20/2014	02
13	201400124	01/20/2014	02
14
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 01/27/2014, ME753339
Dim r As Long, lr As Long, n As Long, h As String
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
  With Cells(r, 1)
    .Value = Trim(Cells(r, 1))
    .Value = Replace(Cells(r, 1), " ", "")
    n = .Value
    .NumberFormat = "General"
    .Value = n
  End With
  With Cells(r, 2)
    .Value = Trim(Cells(r, 2))
    .Value = Replace(Cells(r, 2), " ", "")
    h = .Value
    .NumberFormat = "mm/dd/yyyy"
    .Value = Mid(h, 1, 2) & "/" & Mid(h, 3, 2) & "/" & Mid(h, 5, 4)
  End With
  With Cells(r, 3)
    .Value = Trim(Cells(r, 3))
    .Value = Replace(Cells(r, 3), " ", "")
  End With
  n = Cells(r, 3).Value
  If n > 1 Then
    Rows(r + 1).Resize(n - 1).Insert
    Cells(r + 1, 1).Resize(n - 1, 2).Value = Cells(r, 1).Resize(, 2).Value
    Cells(r, 3).Copy Cells(r + 1, 3).Resize(n - 1)
  End If
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[rawjdog]

Thanks guys for contributing!
Hiker, that's pretty amazing code you figured out there!


I have been toying around with this code and learning a bunch reading in the MSDN about all the code words you used.

yet, having a few issues, I am using excel 2013 if it matters:


1. the code that cleans up the date,

With Cells(r, 2)
.Value = Trim(Cells(r, 2))
.Value = Replace(Cells(r, 2), " ", "")
h = .Value
.NumberFormat = "mm/dd/yyyy"
.Value = Mid(h, 1, 2) & "/" & Mid(h, 3, 2) & "/" & Mid(h, 5, 4)
End With

seems to be dropping the leading zero during the conversion and resulting in dates like this:
01172014 is becoming 11/72/014 , (should be 01/17/2014)
01192014 is becoming 11/92/014 , (should be 01/19/2014)

when I debug it is happening between these two lines:
.Value = Replace(Cells(r, 2), " ", "")
h = .Value

maybe we need to put a .format in there to define "01172014" as a string or text or something so it doesn't drop that leading zero when it places the value in?



2. my spreadsheet actually has 25 columns. so trying to figured out how to make the insert code resize to 25 columns wide.

If n > 1 Then
Rows(r + 1).Resize(n - 1).Insert
Cells(r + 1, 1).Resize(n - 1, 25).Value = Cells(r, 1).Resize(, 25).Value
Cells(r, 3).Copy Cells(r + 1, 3).Resize(n - 1)
End If


however, I can't quite figure out what is going in this part:
a.......Rows(r + 1).Resize(n - 1).Insert
b.......Cells(r + 1, 1).Resize(n - 1, 25).Value = Cells(r, 1).Resize(, 25).Value
c.......Cells(r, 3).Copy Cells(r + 1, 3).Resize(n - 1)


here's what I think is going on, please correct me as you may;

line a inserts rows after row r based on n,
line b is making cells(r+1, 1) through (n-1, 25) to be equal to the values in (r,1) through (r,25)
seems that line b is populating the inserted rows, not sure how line c is used.


thanks again for your advice and suggestions!

 

[rawjdog]

Thanks guys for contributing!
Hiker, that's pretty amazing code you figured out there!


I have been toying around with this code and learning a bunch reading in the MSDN about all the code words you used.

yet, having a few issues, I am using excel 2013 if it matters:


1. the code that cleans up the date,

With Cells(r, 2)
.Value = Trim(Cells(r, 2))
.Value = Replace(Cells(r, 2), " ", "")
h = .Value
.NumberFormat = "mm/dd/yyyy"
.Value = Mid(h, 1, 2) & "/" & Mid(h, 3, 2) & "/" & Mid(h, 5, 4)
End With

seems to be dropping the leading zero during the conversion and resulting in dates like this:
01172014 is becoming 11/72/014 , (should be 01/17/2014)
01192014 is becoming 11/92/014 , (should be 01/19/2014)

when I debug it is happening between these two lines:
.Value = Replace(Cells(r, 2), " ", "")
h = .Value

maybe we need to put a .format in there to define "01172014" as a string or text or something so it doesn't drop that leading zero when it places the value in?



2. my spreadsheet actually has 25 columns. so trying to figured out how to make the insert code resize to 25 columns wide.

If n > 1 Then
Rows(r + 1).Resize(n - 1).Insert
Cells(r + 1, 1).Resize(n - 1, 25).Value = Cells(r, 1).Resize(, 25).Value
Cells(r, 3).Copy Cells(r + 1, 3).Resize(n - 1)
End If


however, I can't quite figure out what is going in this part:
a.......Rows(r + 1).Resize(n - 1).Insert
b.......Cells(r + 1, 1).Resize(n - 1, 25).Value = Cells(r, 1).Resize(, 25).Value
c.......Cells(r, 3).Copy Cells(r + 1, 3).Resize(n - 1)


here's what I think is going on, please correct me as you may;

line a inserts rows after row r based on n,
line b is making cells(r+1, 1) through (n-1, 25) to be equal to the values in (r,1) through (r,25)
seems that line b is populating the inserted rows, not sure how line c is used.


thanks again for your advice and suggestions!

 

[Rick Rothstein]

Here is a macro I came up with which I think should work for you also...

Sub FixDataAndDistributeDown()
  Dim X As Long, Z As Long, Index As Long, LastRow As Long, ArrayIn As Variant, ArrayOut As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  ArrayIn = Range("A2:C" & LastRow)
  ReDim ArrayOut(1 To Evaluate("SUM(1*SUBSTITUTE(C2:C" & LastRow & ", "" "",""""))"), 1 To 3)
  For X = 1 To UBound(ArrayIn)
    For Z = 1 To Replace(ArrayIn(X, 3), " ", "")
      Index = Index + 1
      ArrayOut(Index, 1) = Replace(ArrayIn(X, 1), " ", "")
      ArrayOut(Index, 2) = CDate(Format(Replace(ArrayIn(X, 2), " ", ""), "@@/@@/@@@@"))
      ArrayOut(Index, 3) = Format(Replace(ArrayIn(X, 3), " ", ""), "00")
    Next
  Next
  Range("A2:C" & UBound(ArrayOut) + 1) = ArrayOut
  Range("B2:B" & UBound(ArrayOut) + 1).NumberFormat = "mm/dd/yyyy"
  Range("C2:C" & UBound(ArrayOut) + 1).NumberFormat = "00"
End Sub

 

[hiker95]

rawjdog,

I had all my original raw data formatted as text.

Sample raw data:

Excel 2007
	A	B	C
1	Report #	Date	Units Involved
2	2 0 1 4 0 0 1 1 5	1 2 1 6 2 0 1 3	0 2
3	2 0 1 4 0 0 1 1 8	1 2 2 9 2 0 1 3	0 1
4	2 0 1 4 0 0 1 2 0	0 1 0 1 2 0 1 4	0 3
5	2 0 1 4 0 0 1 2 2	0 1 1 3 2 0 1 4	0 4
6	2 0 1 4 0 0 1 2 4	0 1 2 0 2 0 1 4	0 2
7
8
9
10
11
12
13
14
Sheet1

After the updated macro:

Excel 2007
	A	B	C
1	Report #	Date	Units Involved
2	201400115	12/16/2013	02
3	201400115	12/16/2013	02
4	201400118	12/29/2013	01
5	201400120	01/01/2014	03
6	201400120	01/01/2014	03
7	201400120	01/01/2014	03
8	201400122	01/13/2014	04
9	201400122	01/13/2014	04
10	201400122	01/13/2014	04
11	201400122	01/13/2014	04
12	201400124	01/20/2014	02
13	201400124	01/20/2014	02
14
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ReorgDataV2()
' hiker95, 01/28/2014, ME753339
Dim r As Long, lr As Long, n As Long, h As String
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
  With Cells(r, 1)
    .Value = Trim(Cells(r, 1))
    .Value = Replace(Cells(r, 1), " ", "")
    n = .Value
    .NumberFormat = "General"
    .Value = n
  End With
  With Cells(r, 2)
    .Value = Trim(Cells(r, 2))
    .Value = Replace(Cells(r, 2), " ", "")
    h = .Value
    If Len(h) = 7 Then h = "0" & h
    .Value = Mid(h, 1, 2) & "/" & Mid(h, 3, 2) & "/" & Mid(h, 5, 4)
    .NumberFormat = "mm/dd/yyyy"
  End With
  With Cells(r, 3)
    .Value = Trim(Cells(r, 3))
    .Value = Replace(Cells(r, 3), " ", "")
  End With
  n = Cells(r, 3).Value
  Cells(r, 3).NumberFormat = "00"
  If n > 1 Then
    Rows(r + 1).Resize(n - 1).Insert
    Cells(r + 1, 1).Resize(n - 1, 2).Value = Cells(r, 1).Resize(, 2).Value
    Cells(r, 3).Copy Cells(r + 1, 3).Resize(n - 1)
  End If
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgDataV2 macro.

 

[hiker95]

Rick Rothstein,

Nicely done, and, a very interesting approach/resolution - one for my archives.

Thank you very much.

Have you ever thought of publishing all your neat/short programming tricks?

 

[Rick Rothstein]

Rick Rothstein,

Nicely done, and, a very interesting approach/resolution - one for my archives.

Thank you very much.

You are welcome... I am glad you found my code interesting enough to save to your archives... I'm flattered.

Have you ever thought of publishing all your neat/short programming tricks?

Actually, I have thought about writing a book, and I even have a title for it... "Excel VBA - My Way"... and I have also created a list of "titles" for the chapters. Of course that was a year ago and I have done nothing with the idea since. You see, I hate to write, plus I have tendencies toward being obsessive-compulsive, and those coupled together would mean torture to me. You see, I have been published in the past... I have had more than a dozen articles (much shorter than a book) published in the past (6 for the TI-99/4 computer in a magazine called COMPUTE! back in the mid-80s, 4 total for the Radio Shack Model 100 computer in PM and Potable 100/200 magazines back in the mid-80s, 7 for the compiled version of Visual Basic in a newsletter magazine called "Visual Basic Developer" back in the late 1990s, early 2000s, not to mention another 9 tips in various magazines throughout that same span of time)... writing everyone of those was sheer torture. Now keep in mind all of those taken together probably were not as large as the book I have in mind would be. By the way, back in the mid-80s, one of the editors for COMPUTE! asked me to write a book about programming for the TI-99/4, this was back when COMPUTE! was paying me good money for the articles I wrote, so I probably could have made a nice amount for the book... I declined his offer for the very reasons I mentioned above. So I am not sure if I will ever sit down and actually write my book, but it is a thought that I have kept coming back to several times on this past year.