+ Reply to Thread
Results 1 to 7 of 7

Find string in a column and insert a blank row after

  1. #1
    Registered User
    Join Date
    11-05-2020
    Location
    Los Angeles
    MS-Off Ver
    Latest
    Posts
    3

    Find string in a column and insert a blank row after

    I have date that looks like this:
    1212-M-BSS DEFAULT=1000
    1212-M-PSS DEFAULT=1000
    1212-W-BSS DEFAULT=1000
    1212-W-PSS DEFAULT=1000
    1212-X-BSS DEFAULT=1000
    1212-X-PSS DEFAULT=1000

    That I want it to look like this:

    1212-M-BSS DEFAULT=1000
    1212-M-PSS DEFAULT=1000

    1212-W-BSS DEFAULT=1000
    1212-W-PSS DEFAULT=1000

    1212-X-BSS DEFAULT=1000
    1212-X-PSS DEFAULT=1000


    So I want to target all the PSS and insert a row after that. And also there are other columns in a row that has PSS.
    Making sure that it only insert one blank row per any found PSS in a row.

    All helps are appreciated in advance.

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Find string in a column and insert a blank row after

    Attached does it:

    Please Login or Register  to view this content.
    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 12-18-2020 at 08:37 PM.

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Find string in a column and insert a blank row after

    Why Dim statements (variable declarations) outside the procedure? What advantages does global scope provide in this case?

  4. #4
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Find string in a column and insert a blank row after

    hringry,

    I always put them outside because most times I have more than one Sub (e.g. Most database operations need "ADD", "FIND", "UPDATE" and "CLEAR" buttons on the Input sheet or User Form, Why repeat the variables needlessly?

    Ochimus

  5. #5
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Find string in a column and insert a blank row after

    Quote Originally Posted by Ochimus View Post
    . . . Why repeat the variables needlessly? . . .
    Because VBA procedures can call other VBA procedures, and if Sub a sets variable x then calls Sub b which also sets varaible x, then when Sub b ends and control returns to Sub a, the value of x in Sub a has been lost. The overhead needed to preserve variable values when calling other procedures is greater than the burden of declaring local variables in each procedure.

    Sub a doesn't know which variables Sub b uses/changes.

    Yes, you may be able to get away with this approach, but it doesn't scale up well.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find string in a column and insert a blank row after

    Quote Originally Posted by alexiirj View Post
    So I want to target all the PSS and insert a row after that. And also there are other columns in a row that has PSS.
    Making sure that it only insert one blank row per any found PSS in a row.

    All helps are appreciated in advance.
    Try
    Please Login or Register  to view this content.

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Find string in a column and insert a blank row after

    Assuming data is in A2:A7

    first cell C2: = A2

    C3:

    Please Login or Register  to view this content.
    Drag down
    Attached Images Attached Images
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA string for dragging data to last blank row in a series of blank cells in a column
    By JDGRN5008 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-25-2019, 02:42 PM
  2. [SOLVED] Find non blank cell in a column (range Specified) and insert formula to other cell in same
    By jpbisani in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-18-2018, 06:11 AM
  3. Replies: 4
    Last Post: 03-30-2015, 03:20 PM
  4. Replies: 1
    Last Post: 09-11-2014, 10:59 AM
  5. Find string and save...insert another string
    By twckfa16 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-21-2013, 04:48 PM
  6. Find a string and then insert new row above the SUM Formula
    By antoni in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-23-2009, 07:47 AM
  7. how to find Certain String in a column, select/Copy the cell till another String
    By excelkeechak in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-18-2009, 10:59 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1