Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-24-2014, 05:25 AM
beginner beginner is offline Delete blank rows between the two rows that contain data Windows 7 32bit Delete blank rows between the two rows that contain data Office 2007
Advanced Beginner
Delete blank rows between the two rows that contain data
 
Join Date: Sep 2011
Location: Europe
Posts: 42
beginner will become famous soon enough
Unhappy Delete blank rows between the two rows that contain data

This file in the future will be read-only or as a template.


I need VBA to when saving files (SaveAs) but before saving the macro should delete all blank rows between the two rows containing data.

Can anybody help me?
Please see attach
Attached Files
File Type: xls delete-empty-rows.xls (31.5 KB, 8 views)
Reply With Quote
  #2  
Old 12-24-2014, 09:50 AM
beginner beginner is offline Delete blank rows between the two rows that contain data Windows 7 32bit Delete blank rows between the two rows that contain data Office 2007
Advanced Beginner
Delete blank rows between the two rows that contain data
 
Join Date: Sep 2011
Location: Europe
Posts: 42
beginner will become famous soon enough
Default

I have tried this VBA macro
Code:
Sub Macro3()
'Select empty rows in range and delete them
    Range("A10:H50").Select 'this range is the differencing
    Selection.SpecialCells(xlCellTypeBlanks).Select 'selecting empty rows
    ActiveWindow.SmallScroll Down:=18 '??????
    Selection.Delete Shift:=xlUp 'search last row with data?
    ActiveWindow.SmallScroll Down:=-27 '????
    Range("A1").Select 'position when VBA is finished
End Sub
But I have a problem when the VBA using the second case.
How to rearrange VBA when I can use for both cases.
How to create a universal range and delete "X?" empty rows that change from case to case.
Reply With Quote
  #3  
Old 12-24-2014, 11:40 PM
gmayor's Avatar
gmayor gmayor is offline Delete blank rows between the two rows that contain data Windows 7 64bit Delete blank rows between the two rows that contain data Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 3,175
gmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of light
Default

The following will work with your examples:

Code:
Sub DeleteEmptyRows()
Dim rng As Range
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long
    With ActiveSheet
        LastRow = .Cells(.rows.Count, "G").End(xlUp).Row        'G is the column with the last row of data
        LastCol = .Cells(10, .Columns.Count).End(xlToLeft).Column        '10 is the first row of the range
        Set rng = .Range("A10:H" & LastRow)        'Set the range to the area to be processed
        For i = LastRow To 10 Step (-1)        'Process from the bottom
            'remove the empty rows
            If WorksheetFunction.CountA(rng.rows(i)) = 0 Then rng.rows(i).Delete
        Next
    End With
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #4  
Old 12-25-2014, 02:00 AM
beginner beginner is offline Delete blank rows between the two rows that contain data Windows 7 32bit Delete blank rows between the two rows that contain data Office 2007
Advanced Beginner
Delete blank rows between the two rows that contain data
 
Join Date: Sep 2011
Location: Europe
Posts: 42
beginner will become famous soon enough
Default

@gmayor thank you for answer
I now see that I was wrong from the start. I did not say formulas that are in range.
Therefore, some cells contain formulas that have been copied to the last row in the range. However, I want SaveAs file but before save I want delete the rows that are empty or contain this formula because the formula display zero as a result.
Can you rearrange VBA (please you see attachment file)
Thanks advance

by the way - url link to your website in your signature is incorrect.
Attached Files
File Type: xls delete-empty-rows2.xls (64.0 KB, 10 views)
Reply With Quote
  #5  
Old 12-25-2014, 06:10 AM
gmayor's Avatar
gmayor gmayor is offline Delete blank rows between the two rows that contain data Windows 7 64bit Delete blank rows between the two rows that contain data Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 3,175
gmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of lightgmayor is a glorious beacon of light
Default

It needs a minor adjustment to account for that :
Code:
Sub DeleteEmptyRows()
'Graham Mayor
Dim rng As Range
Dim i As Long
Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row - 4        'G is the column with the last row of data
        Set rng = .Range("A1:D" & LastRow)        'Set the range to the area to be processed
        For i = LastRow To 10 Step -1        'Process from the bottom
            If WorksheetFunction.CountA(rng.Rows(i)) = 0 Then rng.Rows(i).EntireRow.Delete
        Next i
    End With
End Sub
Thanks for the heads up about the link. There was an unwanted space in the link. Now it should be fixed.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #6  
Old 12-26-2014, 12:29 AM
beginner beginner is offline Delete blank rows between the two rows that contain data Windows 7 32bit Delete blank rows between the two rows that contain data Office 2007
Advanced Beginner
Delete blank rows between the two rows that contain data
 
Join Date: Sep 2011
Location: Europe
Posts: 42
beginner will become famous soon enough
Default Solved

Quote:
Originally Posted by gmayor View Post
It needs a minor adjustment to account for that :
Thank you very much Graham
[problem SOLVED]

Last edited by beginner; 12-26-2014 at 12:32 AM. Reason: SOLVED
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete blank rows between the two rows that contain data Delete All empty Rows - Print - Undo all Rows deleted Bathroth Word VBA 1 10-01-2014 01:40 PM
Delete blank rows between the two rows that contain data Grouping table rows to prevent individual rows from breaking across pages dennist77 Word 1 10-29-2013 11:39 PM
Delete blank rows between the two rows that contain data Delete Blank Rows (Cyrillic Text in Spreadsheet ) dozd Excel 1 02-22-2013 03:24 AM
Delete blank rows between the two rows that contain data Count rows and add blank rows accordingly Hoochtheseal Word VBA 1 01-29-2013 09:23 PM
Delete blank rows between the two rows that contain data How to remove blank rows from a specified range? Learner7 Excel 1 04-19-2011 02:45 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 11:40 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft