Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Beginner Seeking Help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2017
    Posts
    30
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Beginner Seeking Help

    Hello,

    I have a macro that use the autofilter function to find blanks and then deletes the rows. However, I am struggling with how to write a macro that will find the last column in row 10 and filter on zero and delete all 0's. Then after it executes this code, I want 'that' entire column deleted. Any ideas?

    This is the code I have, but is specifically looks at Column C:

    Sub DeleteRowsC()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Journals")
    ws.Activate
    On Error Resume Next
    ws.ShowAllData
    On Error GoTo 0

    ws.Range("A10:BZ10000").AutoFilter Field:=3, Criteria1:=""

    Application.DisplayAlerts = False
    ws.Range("A10:BZ10000").SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True

    On Error Resume Next
    ws.ShowAllData
    On Error GoTo 0

    End Sub

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,928
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Beginner Seeking Help

    I am probably misreading your post, but if you get the last column, delete the zeros and then delete the column, why not just delete the column to begin with?
    Code:
    Sub t()
    With ActiveSheet
        . Columns(.Cells(10, Columns.Count).End(xlToLeft).Column).Delete xlShiftLeft
    End With
    End Sub
    Last edited by JLGWhiz; Apr 20th, 2019 at 05:17 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    7,443
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Beginner Seeking Help

    Are you wanting to DELETE Column of Row 10 Only?
    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  4. #4
    New Member
    Join Date
    May 2017
    Posts
    30
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Beginner Seeking Help

    Quote Originally Posted by jim may View Post
    Are you wanting to DELETE Column of Row 10 Only?
    Hello,

    What I am doing in my last row is adding up columns D:End to identify rows with all zero's. I then want to filter on 0 and delete all rows with 0 in this column. Then I want to delete that column as it is not in my original dataset.

  5. #5
    New Member
    Join Date
    Nov 2014
    Location
    Luxembourg
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Beginner Seeking Help

    Wouldn't just changing the 3 to a 10 work?

    ws.Range("A10:BZ10000").AutoFilter Field:=3, Criteria1:=""

    ws.Range("A10:BZ10000").AutoFilter Field:=10, Criteria1:=0

    Then delete your rows and then do Range("j:j").delete

    I think that will give you what you've asked for.
    Last edited by higrm; Apr 20th, 2019 at 06:31 PM.

  6. #6
    New Member
    Join Date
    Nov 2014
    Location
    Luxembourg
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Beginner Seeking Help

    Oops, misread the row 10 as column 10. Ok, find the last cell in row 10 by going all the way to the right of column then and then back to the first filled cell. Once you have that cell, use it's column reference to adjust your autofilter line as above.

    Code:
    Sub xxx()
    
        Dim lCol As Long
        lCol = Cells(10, Columns.Count).End(xlToLeft).Column
        Range("A10:BZ10000").AutoFilter Field:=lCol, Criteria1:=0   
        Range("A10:BZ10000").SpecialCells(xlCellTypeVisible).Delete
        Range(Cells(1,10),cells(1,10)).column.delete
    End Sub
    I'm guessing on the last line whether that will get you the column ok.
    Last edited by higrm; Apr 20th, 2019 at 06:45 PM.

  7. #7
    New Member
    Join Date
    Nov 2014
    Location
    Luxembourg
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Beginner Seeking Help

    correct last line:
    Code:
    Sub xxx()
    
        Dim lCol As Long
        lCol = Cells(10, Columns.Count).End(xlToLeft).Column
        Range("A10:BZ10000").AutoFilter Field:=lCol, Criteria1:=0   
        Range("A10:BZ10000").SpecialCells(xlCellTypeVisible).Delete
        Range(Cells(1, lCol), Cells(1, lCol)).EntireColumn.Delete
    End Sub

  8. #8
    New Member
    Join Date
    May 2017
    Posts
    30
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Beginner Seeking Help

    Quote Originally Posted by higrm View Post
    correct last line:
    Code:
    Sub xxx()
    
        Dim lCol As Long
        lCol = Cells(10, Columns.Count).End(xlToLeft).Column
        Range("A10:BZ10000").AutoFilter Field:=lCol, Criteria1:=0   
        Range("A10:BZ10000").SpecialCells(xlCellTypeVisible).Delete
        Range(Cells(1, lCol), Cells(1, lCol)).EntireColumn.Delete
    End Sub
    hello, this works great. However, it appears to only be deleting my top row and not all with row with zero. From my understanding the codes seems to be written correctly. Any ideas on why this may be?

  9. #9
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,442
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Beginner Seeking Help

    @Viking1221
    You didn't respond to the post from @JLGWhiz.....which to me, seems to be the easiest and simplest method


    Code:
    Sub t()
    With ActiveSheet
        . Columns(.Cells(10, Columns.Count).End(xlToLeft).Column).Delete xlShiftLeft
    End With
    End Sub
    Last edited by Michael M; Apr 20th, 2019 at 07:52 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  10. #10
    New Member
    Join Date
    May 2017
    Posts
    30
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Beginner Seeking Help

    Quote Originally Posted by Viking1221 View Post
    hello, this works great. However, it appears to only be deleting my top row and not all with row with zero. From my understanding the codes seems to be written correctly. Any ideas on why this may be?
    Never mind, I figured it out, it is the way I have the numbers formatted. Zero's show as "-" I just had to change the 0 to a - and it worked. Thanks for your help.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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