Need short Macro for formatting task

TeachMeToExcel

New Member
Joined
Feb 4, 2016
Messages
16
THANK YOU SO MUCH FOR ANY TIME AND EFFORT IN HELPING ME SAVE MANY MAN HOURS AT WORK!!!!

I just need to take a large data set from the first format below to the second. The changes include:
1. New column
2. Each row from the original data set that begins with cell A containing "RE: $ (number)", that cell and the proceeding B cell are moved to new column as pictured
3. Row from which they were moved is deleted
4. Trim function for all data in the set, since original format often includes spaces before data.

Before Formatting:

RE: $ 157,925,000PENNSYLVANIA HOUSING FINANCE AGENCY
Belle Haven Investments
10/1/2032​
1.65​
5​
90​
450​
70879QKR3
Blackrock Financial Management
4/1/2022​
5​
2.5​
50​
125​
70879QKY8
Boyd Watterson Asset Management
4/1/2023​
5​
2.5​
10​
25​
70879QLA9
Citizens Bank
10/1/2027​
0.8​
5​
3.75​
18.75​
70879QKF9
Veritable Investment Consultants, LP
4/1/2029​
1.1​
5​
10​
50​
70879QKJ1
RE: $ 43,410,000MONTANA BOARD OF HOUSING
Capital Research
6/1/2051​
3​
5​
1,410​
7,050.00​
61212WMU6
First and Peoples Bank & Trust
12/1/2041​
2​
6.25​
2​
12.5​
61212WMR3
Kemper Corporation
12/1/2050​
2.125​
6.25​
58.5​
365.63​
61212WMT9

After formatting:

RE: $ 157,925,000Blackrock Financial Management
4/1/2022​
5​
2.5​
50​
125​
70879QKY8
PENNSYLVANIA HOUSING FINANCE AGENCYBoyd Watterson Asset Management
4/1/2023​
5​
2.5​
10​
25​
70879QLA9
Citizens Bank
10/1/2027​
0.8​
5​
3.75​
18.75​
70879QKF9
Veritable Investment Consultants, LP
4/1/2029​
1.1​
5​
10​
50​
70879QKJ1
RE: $ 43,410,000Capital Research
6/1/2051​
3​
5​
1,410​
7,050.00​
61212WMU6
MONTANA BOARD OF HOUSINGFirst and Peoples Bank & Trust
12/1/2041​
2​
6.25​
2​
12.5​
61212WMR3
Kemper Corporation
12/1/2050​
2.125​
6.25​
58.5​
365.63​
61212WMT9
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try this:

Your data in Sheet1 starting in cell A1. The results on Sheet2.

VBA Code:
Sub formatting_task()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Sheets("Sheet1").Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
  j = 1
  For i = 1 To UBound(a, 1)
    If UCase(Left(a(i, 1), 3)) = "RE:" Then
      b(j, 1) = WorksheetFunction.Trim(a(i, 1))
      b(j + 1, 1) = WorksheetFunction.Trim(a(i, 2))
    Else
      For k = 1 To UBound(a, 2)
        b(j, k + 1) = WorksheetFunction.Trim(a(i, k))
      Next
      j = j + 1
    End If
  Next
  Sheets("Sheet2").Range("A1").Resize(j, UBound(b, 2)).Value = b
End Sub
 
Upvote 0
Try this:

Your data in Sheet1 starting in cell A1. The results on Sheet2.

VBA Code:
Sub formatting_task()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
 
  a = Sheets("Sheet1").Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
  j = 1
  For i = 1 To UBound(a, 1)
    If UCase(Left(a(i, 1), 3)) = "RE:" Then
      b(j, 1) = WorksheetFunction.Trim(a(i, 1))
      b(j + 1, 1) = WorksheetFunction.Trim(a(i, 2))
    Else
      For k = 1 To UBound(a, 2)
        b(j, k + 1) = WorksheetFunction.Trim(a(i, k))
      Next
      j = j + 1
    End If
  Next
  Sheets("Sheet2").Range("A1").Resize(j, UBound(b, 2)).Value = b
End Sub
This added a new instance in Sheet2, trimmed the data, created a new column, but did not move the "RE: $ 157,925,000" and the "PENNSYLVANIA HOUSING FINANCE AGENCY" into the new column/rows or delete the row from which they came. Thanks!
 
Upvote 0
2. Each row from the original data set that begins with cell A containing "RE: $ (number)"

Maybe you don't have "RE:" as you mentioned at the beginning.
With this data on sheet1.

Dante Amor
ABCDEFG
1RE: $ 157,925,000PENNSYLVANIA HOUSING FINANCE AGENCY
2Belle Haven Investments10/01/20321.6559045070879QKR3
3Blackrock Financial Management04/01/202252.55012570879QKY8
4Boyd Watterson Asset Management04/01/202352.5102570879QLA9
5Citizens Bank10/01/20270.853.7518.7570879QKF9
6Veritable Investment Consultants, LP04/01/20291.15105070879QKJ1
7RE: $ 43,410,000MONTANA BOARD OF HOUSING
8Capital Research06/01/2051351,4107,050.0061212WMU6
9First and Peoples Bank & Trust12/01/204126.3212.561212WMR3
10Kemper Corporation12/01/20502.136.358.5365.6361212WMT9
Sheet1


The result is on sheet2.
Dante Amor
ABCDEFGH
1RE: $ 157,925,000Belle Haven Investments01/10/20321.6559045070879QKR3
2PENNSYLVANIA HOUSING FINANCE AGENCYBlackrock Financial Management01/04/202252.55012570879QKY8
3Boyd Watterson Asset Management01/04/202352.5102570879QLA9
4Citizens Bank01/10/20270.853.7518.7570879QKF9
5Veritable Investment Consultants, LP01/04/20291.15105070879QKJ1
6RE: $ 43,410,000Capital Research01/06/2051351410705061212WMU6
7MONTANA BOARD OF HOUSINGFirst and Peoples Bank & Trust01/12/204126.25212.561212WMR3
8Kemper Corporation01/12/20502.1256.2558.5365.6361212WMT9
Sheet2


Try again with the following macro. If you have problems, then you could put the data with which you are testing here, but use the XL2BB tool minisheet, to have a sample closer to the reality of your data.

VBA Code:
Sub formatting_task()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, lr As Long, lc As Long
  Dim dato As String
  
  With Sheets("Sheet1")
    lr = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    lc = .UsedRange.Columns(.UsedRange.Columns.Count).Column
    a = .Range("A1", .Cells(lr, lc)).Value
  End With
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
  j = 1
  For i = 1 To UBound(a, 1)
    dato = UCase(Replace(a(i, 1), " ", ""))
    If InStr(1, dato, "RE:") > 0 Then
      b(j, 1) = WorksheetFunction.Trim(a(i, 1))
      b(j + 1, 1) = WorksheetFunction.Trim(a(i, 2))
    Else
      For k = 1 To UBound(a, 2)
        b(j, k + 1) = WorksheetFunction.Trim(a(i, k))
      Next
      j = j + 1
    End If
  Next
  Sheets("Sheet2").Range("A1").Resize(j, UBound(b, 2)).Value = b
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top