Posts by _donald

    Re: Delete Excel Rows Using Auto Filtering .SpecialCells(xlCellTypeVisible).EntireRow


    When I get to this line while steping through the code:


    Code
    1. .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete


    For strCriteria3


    It does not delete visible cells. It does for you?


    Code
    1. With rRange 'Filter, offset(to exclude headers) and delete visible rows
    2. .AutoFilter Field:=Col1, Criteria1:=strCriteria3
    3. .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    4. End With


    This is what I am left with:
    [ATTACH=CONFIG]44316[/ATTACH]

    Re: Delete Excel Rows Using Auto Filtering .SpecialCells(xlCellTypeVisible).EntireRow


    Hey Dangle,


    Thanks.


    Attached is the .xlsm and the .txt that is uses.


    Donald


    ozgrid.com/forum/core/index.php?attachment/44314/ozgrid.com/forum/core/index.php?attachment/44315/

    in reference to http://www.ozgrid.com/VBA/row-delete-criteria.htm


    Specifically:


    Code
    1. With rRange 'Filter, offset(to exclude headers) and delete visible rows
    2. .AutoFilter Field:=Col1, Criteria1:=strCriteria1
    3. .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    4. End With


    Code works great for deleting visible rows until i try to remove rows based on a criteria of:

    Code
    1. strCriteria3 = "*----*"


    The data filters correctly using this criteria but when I get to the:


    Code
    1. .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete


    Portion of the code. The rows are not deleted.


    Do you know of any issues with having nothing but


    [TABLE="width: 1000"]

    [tr]


    [td]

    ------

    [/td]


    [td]

    -------------------------

    [/td]


    [td]

    --------------------------------

    [/td]


    [td]

    -----------------------------------------------------------

    [/td]


    [/tr]


    [/TABLE]


    in a row and why this code would fail on those lines?


    Thank you,
    Donald

    Re: Importing AS400 Reports and Appending Header Data


    Attached is what I wound up doing if anyone is interested.


    It takes a very long time to run so i'm sure my coding isn't very efficient.
    ozgrid.com/forum/core/index.php?attachment/44282/


    Hey All,


    I am importing text reports from an AS400 into excel.


    I have the import code down and have cleaned up the data so only relevant things remain.


    I am not that great with VBA and am stuck on appending the header detail to the individual records.


    Attached is what I have thus far in EU_IPI_FILE.xlsm
    Source Report is PRT014G5511212.txt


    the Only_Relevant_Data tab is where I am currently at.
    the append tab is what I need to get to.


    ozgrid.com/forum/core/index.php?attachment/44188/ozgrid.com/forum/core/index.php?attachment/44189/


    Any help is much appreciated.


    Thank you,
    Donald

    Re: Insert Pictures with varying naming convention from a list of a specific length V


    Thank you very much.


    It's great to get different POV on how to solve this and to also see efficient structure that I can add into my code going forward.


    jindon: Your code is the one I'm going to use. I am going to add a couple more directories to search if there is no file found. Thanks.


    dbrown14: Your code works but because of the number of files in the directory it takes a very long time to execute. Thank you for taking a crack at it. I should have specified the directory is quite large in the number of files on it.


    Krishnakumar: thank you for fixing my post.


    Glad I posted and have graduated from lurking this forum for years to actually participating.


    Donald

    This is my first post please be gentle... I am trying to "import" pictures into an excel sheet.I have data in column B that i am adding at the end of a directory string in A1 and then adding the file extension in B1 to return the image. Formula is: =$A$1&A2&$B$1result is: \\inside\ProductImageLibrary\Images\10061.jpg I am then using this with ActiveSheet.Pictures.insert to insert the picture into my sheet. The issue i have is some of pictures have additional attributes in the file name. i.e. 49451_100.jpg I have tried to solve for this using wildcards and extensive google searching to no avail. Ultimate goal with this code would be to bring back the first file that contains the data. I don't know what the additional attributes will be, but I do know somewhere in the name there will be the data 49451 image files for 49451 are:49451_100.jpg 49451_359.jpg 49451_434.jpg etc etc so far this is the code I have.


    Files

    • get_pics.txt

      (579 Byte, downloaded 44 times, last: )