+ Reply to Thread
Results 1 to 16 of 16

VBA Check number of empty rows after a character and add or delete if necessary

  1. #1
    Registered User
    Join Date
    04-08-2012
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2007
    Posts
    8

    VBA Check number of empty rows after a character and add or delete if necessary

    Hi, I did trawl through a few hundred similar topics but could not find my answer.. here goes;

    I am using Excel 2007 on a win7 machine and I am manipulating data I cut and paste into a spreadsheet.

    I need to check the number of empty rows after the character "Kg" or "L" or "CL" that is preceeded by a number.. so it could be "1 Kg" or 0,5 Kg" or "0,5 L" but there seems to always be a space before the Kg or L if that helps.

    I need there to be 6 empty rows between the Kg and the cell that contains a price.. for example "3.24€"

    All my data is in Col "A"

    example data:

    A1:item description
    A2: empty row
    A3:brand name
    A4: empty row
    A5: empty row
    A6:1 Kg

    what i need it to be is:


    A1:item description
    A2: empty row
    A3:brand name
    A4: empty row
    A5: empty row
    A6: empty row
    A7: empty row
    A8: empty row
    A9: empty row
    A6:1 Kg


    I might add that the number of rows above the Kg or L or Cl is not constant so I couldnt attack the problem by deleting the empty rows and simply adding the desired number of rows.

    I also add that the number of rows after the weight measure might be 2 or 3 or 4, etc but as I said I need it to be six due to some pre written vba I have.


    what I am trying to do is align horizontaly that data and it seems that the constant is the weight part of my data.
    thanks in advance
    Last edited by frjcd; 04-08-2012 at 03:59 PM.

  2. #2
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    hi frjcd, welcome to Excelforum, can you post sample file with original data and result you need to obtain. What is the number of data rows to deal with?

  3. #3
    Registered User
    Join Date
    04-08-2012
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    hi watersev,

    i am not sure the number of rows concerned but I could cut it into 'lots' if required but may be in the thousands.


    Here is a sample spreadsheet of the raw data along with a required result spreadsheet.

    JCD
    Attached Files Attached Files
    Last edited by frjcd; 04-08-2012 at 04:27 PM.

  4. #4
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    how many empty rows should follow price line?

  5. #5
    Registered User
    Join Date
    04-08-2012
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    6 empty rows between the price and the description

  6. #6
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    EDIT: Bug is fixed. this should work.

    Please Login or Register  to view this content.
    Last edited by Mallycat; 04-08-2012 at 05:15 PM.

  7. #7
    Registered User
    Join Date
    04-08-2012
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    hi malleycat, thanks for the code. It is still looping but I wondered if you could adjust the code to get the data to align horizontaly (see my required_result xls)

    for the moment I use;
    Cells.Find(What:="Le Surgelé", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Delete Shift:=xlUp
    Cells.Find(What:="Mes boutiques", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Delete Shift:=xlUp

    to get rid of the titles..

    then i use your code to get the correct number of rows and then I use this code to align:

    ActiveCell.Rows("1:6").EntireRow.Select
    Selection.Delete Shift:=xlUp
    ActiveCell.Offset(9, 0).Range("A1").Select
    Selection.Cut
    ActiveCell.Offset(-9, 3).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(4, -3).Range("A1").Select
    Selection.Cut
    ActiveCell.Offset(-4, 2).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(2, -2).Range("A1").Select
    Selection.Cut
    ActiveCell.Offset(-2, 1).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Rows("1:9").EntireRow.Select
    Selection.Delete Shift:=xlUp
    ActiveCell.Select

    Still doesnt work though as my data seems to get misaligned...

  8. #8
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    This code seems to work now.
    Please Login or Register  to view this content.

  9. #9
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    What do you mean by "align" the text. Can you please post a sample of what the final output should look like.

  10. #10
    Registered User
    Join Date
    04-08-2012
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    Hi Malleycat,

    the file (see post #3) called required_result contains how the data should look.

    can you see this file ok ?

    basically, instead of everything being in col "A" as it is now it should be:

    A1=description1
    B1=brand
    C1=weight
    D1=price

    A2=description2
    B2=brand
    C2=weight
    D2=price

    and so on..

    thanks for your help
    Last edited by frjcd; 04-08-2012 at 05:54 PM.

  11. #11
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    Give me a few minutes

  12. #12
    Registered User
    Join Date
    04-08-2012
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    thanks

    I have been going around in circles for quite a while before posting.. I thought that making the rows between those characters even would help me run my code of aligning the data but I cant seem to get it right
    Last edited by frjcd; 04-08-2012 at 06:18 PM.

  13. #13
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    ok, try this

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    04-08-2012
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    WOW, works a treat, thank you SOOOOO Much!

  15. #15
    Registered User
    Join Date
    04-08-2012
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    quick question, was the fact of sending the data to another sheet the big difference?

  16. #16
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: VBA Check number of empty rows after a character and add or delete if necessary

    No, Once I knew what you wanted, I took a completely different approach. It was just easier to put it into a new sheet. I sort of knew that the first request for fixing the spacing between the weight was unlikely to be the final solution to what you needed, but it was an interesting problem anyway. It is always best to describe your problem rather than what you think the solution needs to be :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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