Delete rows beginning with ! and insert row with text into multiple sheets

spencer_time

New Member
Joined
Sep 19, 2019
Messages
41
Hello,

I am trying to make a complex spreadsheet that will import .s2p files (just text files with a different extension) containing some data, each to a different sheet, delete the beginning comment rows, add a row that has headings describing the data.

All of that for the raw data files (between 1 and ~30 files), a reference file, and an ideal file. I then want to so math on each of the data files with the reference file. After all that I need to do math on the new data with regards to the ideal file and make plots.

I have gotten as far as importing the -space- delimited .s2p files by copying and editing code I've found on the internet. I need help deleting the comment rows (all the first rows that begin with ! can be anywhere from 0 to 12 from what I've seen, and possibly more, as there is no practical limit) and adding a row with headings (instead of leaving a row and overwriting the cells, causing a text box that warns that there is already data here).

Any other suggestions about how I butchered the code together and/or any improvements I could make is also welcome.

Code:
Sub import_multiple_s2p()
    Dim xFilesToOpen As Variant
    Dim I As Integer
    Dim xWb As Workbook
    Dim xTempWb As Workbook
    Dim xDelimiter As String
    Dim xScreen As Boolean
    xScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False
    xDelimiter = "|"
    xFilesToOpen = Application.GetOpenFilename("Text Files (*.s2p), *.s2p", , "Import *.s2p files", , True)
    If TypeName(xFilesToOpen) = "Boolean" Then
        MsgBox "No files were selected", , "Import *.s2p files"
        GoTo ExitHandler
    End If
    I = 1
    Set xTempWb = Workbooks.Open(xFilesToOpen(I))
    xTempWb.Sheets(1).Copy
    Set xWb = Application.ActiveWorkbook
    xTempWb.Close False
    xWb.Worksheets(I).Columns("A:A").TextToColumns _
      Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=True, _
      Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=True, _
      Other:=True, OtherChar:="|"
      'Rows("1:6").Delete
      

    Do Until ActiveCell.Value = "#"
        Selection.EntireRow.Delete
    Loop
      
      Cells(1, 1).Value = "MHZ"
      Cells(1, 2).Value = "S11 MAGNITUDE"
      Cells(1, 3).Value = "S11 PHASE"
      Cells(1, 4).Value = "S21 MAGNITUDE"
      Cells(1, 5).Value = "S21 PHASE"
      Cells(1, 6).Value = "S12 MAGNITUDE"
      Cells(1, 7).Value = "S21 PHASE"
      Cells(1, 8).Value = "S22 MAGNITUDE"
      Cells(1, 9).Value = "S22 PHASE"
      Range("A2", Range("A2").End(xlDown)).NumberFormat = "0000"
      Range("B2", Range("B2").End(xlDown)).NumberFormat = "00.000000"
      Range("D2", Range("D2").End(xlDown)).NumberFormat = "00.000000"
      Range("F2", Range("F2").End(xlDown)).NumberFormat = "00.000000"
      Range("H2", Range("H2").End(xlDown)).NumberFormat = "00.000000"
      Range("C2", Range("C2").End(xlDown)).NumberFormat = "000.0000"
      Range("E2", Range("E2").End(xlDown)).NumberFormat = "000.0000"
      Range("G2", Range("G2").End(xlDown)).NumberFormat = "000.0000"
      Range("I2", Range("I2").End(xlDown)).NumberFormat = "000.0000"
    Do While I < UBound(xFilesToOpen)
        I = I + 1
        Set xTempWb = Workbooks.Open(xFilesToOpen(I))
        With xWb
            xTempWb.Sheets(1).Move after:=.Sheets(.Sheets.Count)
            .Worksheets(I).Columns("A:A").TextToColumns _
              Destination:=Range("A1"), DataType:=xlDelimited, _
              TextQualifier:=xlDoubleQuote, _
              ConsecutiveDelimiter:=True, _
              Tab:=False, Semicolon:=False, _
              Comma:=False, Space:=True, _
              Other:=True, OtherChar:=xDelimiter
              'Rows("1:6").Delete
              
                  Do Until ActiveCell.Value = "#"
        Selection.EntireRow.Delete
    Loop
              
              Cells(1, 1).Value = "MHZ"
              Cells(1, 2).Value = "S11 MAGNITUDE"
              Cells(1, 3).Value = "S11 PHASE"
              Cells(1, 4).Value = "S21 MAGNITUDE"
              Cells(1, 5).Value = "S21 PHASE"
              Cells(1, 6).Value = "S12 MAGNITUDE"
              Cells(1, 7).Value = "S21 PHASE"
              Cells(1, 8).Value = "S22 MAGNITUDE"
              Cells(1, 9).Value = "S22 PHASE"
              Range("A2", Range("A2").End(xlDown)).NumberFormat = "0000"
              Range("B2", Range("B2").End(xlDown)).NumberFormat = "00.000000"
              Range("D2", Range("D2").End(xlDown)).NumberFormat = "00.000000"
              Range("F2", Range("F2").End(xlDown)).NumberFormat = "00.000000"
              Range("H2", Range("H2").End(xlDown)).NumberFormat = "00.000000"
              Range("C2", Range("C2").End(xlDown)).NumberFormat = "000.0000"
              Range("E2", Range("E2").End(xlDown)).NumberFormat = "000.0000"
              Range("G2", Range("G2").End(xlDown)).NumberFormat = "000.0000"
              Range("I2", Range("I2").End(xlDown)).NumberFormat = "000.0000"
              
        End With
    Loop
ExitHandler:
    Application.ScreenUpdating = xScreen
    Set xWb = Nothing
    Set xTempWb = Nothing
    Exit Sub    
End Sub
Here is an example of how an .s2p file looks, the formatting is the same (with the first column denoting the frequency, then 8 columns of data following), but the data can go on indefinitely, and there can be any number of comments at the top. It is just a text file with .s2p as the extenstion.

Code:
! 1601_04CEQ_ideal.s2p
! NWS
! 8/26/2019 10:34:24 AM
!
# MHZ S DB R 50
0001000    -11.9869613412018    -6.86095044985507    -11.7757294738579    6.65055268129362            -11.7740869872519    6.50071717047084    -11.9889017811843    -6.90445839318914
0002000    -12.1478395142215    -13.5274223754705    -11.3186368807837    12.1377401054047            -11.3204783726508    12.0457324587022    -12.1495222137791    -13.5759406320280
0003000    -12.3978824218565    -19.9476008912170    -10.7063665310503    16.0365072541540            -10.7055937189204    15.9582753458519    -12.3999585947958    -19.9731161429106
0004000    -12.7254200085112    -25.9770379884654    -10.0569823914036    18.4146148956388            -10.0564514996434    18.3522829857238    -12.7304459315608    -25.9881061936197
0005000    -13.1135576381519    -31.5660628010709    -9.44296340650123    19.5969323463763            -9.44456726844048    19.5519274225073    -13.1180376783935    -31.5947075126734
0006000    -13.5428402263997    -36.7084752745453    -8.90080337061143    19.9502176779632            -8.90126539088865    19.9144986536716    -13.5481450222241    -36.7109975753455
0007000    -14.0009541850627    -41.3693620446857    -8.43551337766472    19.7651437439195            -8.43570593726523    19.7237798351639    -14.0103425884525    -41.3803125119039
0008000    -14.4778426300636    -45.6147278734629    -8.04220978605024    19.2365679833136            -8.04401784682552    19.2011620254331    -14.4887164021327    -45.6218021195332
0009000    -14.9588839942019    -49.4711332825090    -7.71440830089274    18.5194497656443            -7.71607488738760    18.4953692685205    -14.9698456622246    -49.4775408650545
0010000    -15.4397356430685    -52.9522831451455    -7.44006413051596    17.7120443380617            -7.44307423186925    17.6918726983683    -15.4585915952417    -52.9675286257783
0011000    -15.9144772054603    -56.1485861040163    -7.20981625896134    16.8598850762220            -7.21322789456511    16.8407554903229    -15.9353774668016    -56.1609410002211
0012000    -16.3835472409347    -59.0445340846255    -7.01902795986857    16.0182436065222            -7.01889838502051    15.9957313287276    -16.4101992153088    -59.0523068713830
0013000    -16.8348219910265    -61.6887175597529    -6.85585248070910    15.1925205249856            -6.85666703712444    15.1811648706871    -16.8657112044794    -61.6931161079084
0014000    -17.2786322093448    -64.1344229841705    -6.71649251256513    14.3896754121143            -6.71893399052141    14.3834008488113    -17.3154478655748    -64.1491089056643
I will tackle some of the other aspects of this spreadsheet after this is working correctly.

Thanks in advance for any help or advice.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,051
Office Version
365
Platform
Windows
Cross posted https://www.excelforum.com/excel-programming-vba-macros/1290901-delete-rows-beginning-with-add-new-first-row.html#post5199672

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

spencer_time

New Member
Joined
Sep 19, 2019
Messages
41
Okay Fluff, I understand and apologize. I did not read your forum rules before posting. If it is something that is not being answered and I must post it in multiple locations in the future, I will make sure I include a direct link to the other location.

Trent Spencer - KM4YTU
 

spencer_time

New Member
Joined
Sep 19, 2019
Messages
41
I have now read your forum's rules and will follow them in all future posts.

Also, I wanted to edit my last post to say this, but do not see an edit function. Is editing of posts possible on this forum? If so, would you mind telling me how, or where to look for the link to initiate an edit?

Thanks,
Trent Spencer - KM4YTU
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,429
Office Version
365
Platform
Windows
Also, I wanted to edit my last post to say this, but do not see an edit function. Is editing of posts possible on this forum? If so, would you mind telling me how, or where to look for the link to initiate an edit?
Forum regulars have about 10 minutes in which to edit a post (I think newbies may not have that option). After that, you would simply need to post back to the original thread with any clarifications, updates, or corrections.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,051
Office Version
365
Platform
Windows
You can replace this
Code:
    Do Until ActiveCell.Value = "#"
        Selection.EntireRow.Delete
    Loop
      
      Cells(1, 1).Value = "MHZ"
      Cells(1, 2).Value = "S11 MAGNITUDE"
      Cells(1, 3).Value = "S11 PHASE"
      Cells(1, 4).Value = "S21 MAGNITUDE"
      Cells(1, 5).Value = "S21 PHASE"
      Cells(1, 6).Value = "S12 MAGNITUDE"
      Cells(1, 7).Value = "S21 PHASE"
      Cells(1, 8).Value = "S22 MAGNITUDE"
      Cells(1, 9).Value = "S22 PHASE"
      Range("A2", Range("A2").End(xlDown)).NumberFormat = "0000"
      Range("B2", Range("B2").End(xlDown)).NumberFormat = "00.000000"
      Range("D2", Range("D2").End(xlDown)).NumberFormat = "00.000000"
      Range("F2", Range("F2").End(xlDown)).NumberFormat = "00.000000"
      Range("H2", Range("H2").End(xlDown)).NumberFormat = "00.000000"
      Range("C2", Range("C2").End(xlDown)).NumberFormat = "000.0000"
      Range("E2", Range("E2").End(xlDown)).NumberFormat = "000.0000"
      Range("G2", Range("G2").End(xlDown)).NumberFormat = "000.0000"
      Range("I2", Range("I2").End(xlDown)).NumberFormat = "000.0000"
with
Code:
   With Range("A:A")
      .Replace "!*", True, xlWhole, , , , False, False
      On Error Resume Next
      .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
      On Error GoTo 0
   End With
   Range("A1:I1").Value = Array("MHZ", "S11 MAGNITUDE", "S11 PHASE", "S21 MAGNITUDE", "S21 PHASE", "S12 MAGNITUDE", "S21 PHASE", "S22 MAGNITUDE", "S22 PHASE")
   Range("A:A").NumberFormat = "0000"
   Range("B:B,D:D,F:F,H:H").NumberFormat = "00.000000"
   Range("C:C,E:E,G:G,I:I").NumberFormat = "000.0000"
 

spencer_time

New Member
Joined
Sep 19, 2019
Messages
41
Fluff, thank you for your help thus far.

It worked for some .s2p files, but others do not seem to delimit correctly. Also when the headers are being input into the first row, I get a message asking "There's already data here. Do you want to replace it?" with OK/Cancel as options. Below is a sample of one of the s2p files that didn't work, they do not have comments in them. I get this problem whether I upload multiple files, or just one.

Code:
# Hz S DB R 50
1000000    -16.9496242609488    -8.58689662029176    -16.6107105674255    8.20757000102712            -16.6121801059357    7.98110601774455    -16.9028290258889    -8.66753742818949
2000000    -17.160345039084    -16.4903529866769    -15.9980790253834    14.59165897376            -16.0025012756311    14.4256202043857    -17.113823616587    -16.5219217597258
3000000    -17.4569110234875    -24.1547399094944    -15.2220779848417    18.706040552588            -15.2282387099023    18.5873116035894    -17.4077362618371    -24.1307511576949
4000000    -17.8307278351374    -31.2807027225613    -14.4537659135093    20.9083912394711            -14.4581443916628    20.8183242077884    -17.7850771752075    -31.2052091469189
5000000    -18.2635494633286    -37.8606323704411    -13.7671911691377    21.7632657719349            -13.7707058866981    21.682704753608    -18.2229646079218    -37.7603555584193
6000000    -18.730648578772    -43.8725449781662    -13.1847374947529    21.7733591442639            -13.1895055118683    21.7216253589571    -18.6983698884529    -43.7207347699322
7000000    -19.2182762254781    -49.3083973970294    -12.7056772300504    21.3001355585327            -12.7109513563425    21.2493756539912    -19.189886726648    -49.141275758642
8000000    -19.7124864707961    -54.2388671491435    -12.3136884128315    20.5715665149253            -12.3181260117452    20.5296666844449    -19.6881317026912    -54.0687787737426
9000000    -20.1903973055686    -58.7091102681567    -11.9938817868525    19.7241675954174            -12.0000222839416    19.676608677037    -20.1806618278928    -58.5242230646935
10000000    -20.6595854010026    -62.8098038970424    -11.7308619578893    18.8410710335886            -11.7390341192435    18.8096097533336    -20.6637632756176    -62.5531421398315
11000000    -21.1066505338272    -66.507665582555    -11.5149787712039    17.962673281709            -11.5224766508234    17.9490922607454    -21.1244620470981    -66.2714543216958
12000000    -21.5400665660637    -69.9385292007056    -11.3360261904472    17.1358236189079            -11.3421129844749    17.1111870455983    -21.5666862535383    -69.6636542206523
13000000    -21.9459858342634    -73.0643875897233    -11.1866852896227    16.349843282536            -11.1931081558624    16.3347172103154    -21.9885661067405    -72.795847559659
Here is a photo of how the file delimits incorrectly with some files...others work fine. I suspect that it is that in this s2p file the "-" and trailing zeros cause all the columns to not line up perfectly.

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,051
Office Version
365
Platform
Windows
Ok, how about
Code:
Sub Spencertime()
   With Range("A:A")
      .Replace "!*", True, xlWhole, , , , False, False
      On Error Resume Next
      .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
      On Error GoTo 0
   End With
   Range("A1:I1").Value = Array("MHZ ", "S11 MAGNITUDE", "S11 PHASE", "S21 MAGNITUDE", "S21 PHASE", "S12 MAGNITUDE", "S21 PHASE", "S22 MAGNITUDE", "S22 PHASE")
   Range("A2:A1000000").TextToColumns _
      Destination:=Range("A2"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=True, _
      Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=True, _
      Other:=True, OtherChar:="|"
   Range("A:A").NumberFormat = "0000"
   Range("B:B,D:D,F:F,H:H").NumberFormat = "00.000000"
   Range("C:C,E:E,G:G,I:I").NumberFormat = "000.0000"
End Sub
You will need to remove the TextToColumns from your original code.
 

spencer_time

New Member
Joined
Sep 19, 2019
Messages
41
Fluff, again, thank you. You once again have gotten me *closer* to solving this aspect of my spreadsheet program.

I am still a beginner when it comes to coding, so it took me a few tries to figure out what needed to be deleted to enable your suggestion to work.

The problem is now quite a bit stranger than before, it now no longer deletes any of the data, but it still inputs -blank- columns in between the fifth and sixth columns (puts 2 blank columns after column E), but the first row remains unchanged.

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,051
Office Version
365
Platform
Windows
Could you upload a couple of the s2p files to a share site such as OneDrive, DropBox, GoggleDrive. Then mark for sharing & post the link to the thread?
Otherwise it's going to be very difficult to figure out what's going on.
 
Last edited:

Forum statistics

Threads
1,081,679
Messages
5,360,482
Members
400,588
Latest member
SpannersWatson

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top