Posts by _donald

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

It is an issue with the number of records in my range. +20,000 is no good.

I gave you a small data sample.

found this on it:
http://www.ozgrid.com/forum/showthread.php?t=39623

Thanks for your help dangle.

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

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

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

For strCriteria3

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

With rRange 'Filter, offset(to exclude headers) and delete visible rows
      .AutoFilter Field:=Col1, Criteria1:=strCriteria3
      .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    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

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

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

Specifically:

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

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

strCriteria3 = "*----*"

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

.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"]

------

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

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

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

[/TABLE]

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

Thank you,
Donald

Re: Importing AS400 Reports and Appending Header Data

Thanks for the tip.

Going to use this code:

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

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.
forum.ozgrid.com/index.php?attachment/44282/

Private Sub Del_Col_A_CRAP()
 
 ' This macro deletes all rows on the active worksheet
 ' that have X in column Y.
  Dim rng As Range, cell As Range, del As Range
  Dim strCellValue As String
  
  Set rng = Intersect(Range("A2", Range("A" & Rows.Count).End(xlUp)), ActiveSheet.UsedRange)
    For Each cell In rng
    
    strCellValue = (cell.Value)
    
    If strCellValue Like "Company" Or strCellValue Like "I3  REPO" Or strCellValue Like "------" _
    Or strCellValue Like "--------" Or strCellValue Like "PERIOD S" Or strCellValue Like "NO." _
    Or strCellValue Like "ITEM N" Then
    
                     If del Is Nothing Then
                        Set del = cell
                     Else: Set del = Union(del, cell)
                     End If
     End If
  
  Next cell
  
 On Error Resume Next
 
del.EntireRow.Delete




Set del = Nothing
Set cell = Nothing
Set rng = Nothing
strCellValue = vbNullString




On Error GoTo 0
End Sub

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.

forum.ozgrid.com/index.php?attachment/44188/forum.ozgrid.com/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.

Sub get_pics()


Application.ScreenUpdating = False


Dim row As Integer
Dim imgsrc As String, imgdest As String


col = 2
coldes = 3
rowdes = 2


For row = 2 To 181




       imgsrc = Cells(row, col).Value
       imgdst = Cells(rowdes, coldes).Value
       
On Error Resume Next


With ActiveSheet.Pictures.insert(imgsrc)


.Left = Cells(rowdes, coldes).Left
.Top = Cells(rowdes, coldes).Top
        
        col = col
        coldes = coldes
        rowdes = rowdes + 1
End With


Next row


On Error GoTo 0


Application.ScreenUpdating = True


End Sub