+ Reply to Thread
Results 1 to 4 of 4

Removing specific rows when generating my workbooks

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-06-2021
    Location
    Tripoli
    MS-Off Ver
    Still using 2007 in 2023
    Posts
    291

    Wink Removing specific rows when generating my workbooks

    Hello guys,
    I hope you are all fine today.
    I have already completed my vba scripting through which I would generate workbooks based on a standard template.
    The format of all sheets with their respective cell references are totally in harmony with my vba code.
    I need only to amend my code for the purpose of deleting specific rows..Whenever i have in my source file specifically in column Q a zero lineitem quantity, i would like to remove the whole row of that item.

    Please refer to my source file, then have a look on a sample file through which i show you how my final workbook shall be generated.
    Here, you can view into my vba code, that is run inside my template file.

    What amendments shall be made to remove only the those rows with Lineitem quantity zero?

     Sub test()
         Dim rg As Range, i As Long, wb As Workbook
         Dim vNames As Variant, v As Variant
    
         Set rg = Workbooks("source file.csv").Worksheets("source file").UsedRange
    
    'get unique names
        With CreateObject("Scripting.Dictionary")
            For i = 2 To rg.Rows.Count
                .Item(rg.Cells(i, 1).Value) = Empty
            Next i
            vNames = .keys
        End With
    
         Application.DisplayAlerts = False
         Application.ScreenUpdating = False
         For Each v In vNames
              ThisWorkbook.Worksheets("Sheet1").Copy
              Set wb = ActiveWorkbook
              wb.SaveAs v, 51
              rg.AutoFilter 1, v
              rg.Offset(1).Columns("A").SpecialCells(xlCellTypeVisible).Copy wb.Worksheets(1).Cells(2, 2)
              rg.Offset(1).Columns("U").SpecialCells(xlCellTypeVisible).Copy wb.Worksheets(1).Cells(2, 11)
              rg.Offset(1).Columns("Q").SpecialCells(xlCellTypeVisible).Copy wb.Worksheets(1).Cells(2, 13)
              With Worksheets("Sheet1")
                   With .Range("B2:N1014")
                        .Font.Size = 19
                        .Font.Name = "Times New Roman"
                        .HorizontalAlignment = xlCenter
                        .VerticalAlignment = xlCenter
                   End With
                   .Range("C2:C1041").Locked = False
                   .Protect AllowFiltering:=True
                   .EnableSelection = xlUnlockedCells
              End With
              wb.SaveAs ThisWorkbook.Path & "\" & v
              wb.Close
         Next v
         Application.ScreenUpdating = True
         MsgBox "finished"
         Application.DisplayAlerts = True
    End Sub
    Please find attached the following files:
    source data file.
    One sample file named #29390 as the number of its order; i show you through it how i desire to recieve my final generated workbook file.

    Thanks a lot for helping me through this.
    Attached Files Attached Files

  2. #2
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2405-17628.20102
    Posts
    1,402

    Re: Removing specific rows when generating my workbooks

    Maybe on your filter part, instead of just having:
    rg.AutoFilter 1, v
    You could have:
    rg.AutoFilter 1, v
    rg.AutoFilter 20, "="
    If things don't change they stay the same

  3. #3
    Forum Contributor
    Join Date
    11-06-2021
    Location
    Tripoli
    MS-Off Ver
    Still using 2007 in 2023
    Posts
    291

    Re: Removing specific rows when generating my workbooks

     rg.AutoFilter 1, v
              rg.AutoFilter 17, "<>0"
    Hey dear, i managed to solve it by adding this....thanks a lot man

  4. #4
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2405-17628.20102
    Posts
    1,402

    Re: Removing specific rows when generating my workbooks

    Glad you got it sorted

+ 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. Large Data TRANSPOSE BY REMOVING DUPLICATES ON SPECIFIC ROWS
    By Tanujit in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-29-2020, 03:56 PM
  2. Replies: 5
    Last Post: 02-22-2014, 03:45 AM
  3. Removing rows of data that include a specific cell of duplicate info...
    By HanTuco in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-17-2013, 05:57 PM
  4. Replies: 2
    Last Post: 02-06-2012, 05:16 PM
  5. Removing/hiding rows that contain/don't contain specific text.
    By ilikewatchingtv in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 10-09-2007, 08:52 AM
  6. Removing Empty Rows and selecting Specific Rows
    By Jetheat in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-11-2005, 07:10 PM
  7. Replies: 1
    Last Post: 04-01-2005, 07:06 PM

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