Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Remove Rows with heights less than or equal to 1 via macro

0

I receive a worksheet where many rows have different sizes.  Most of the rows with a height of 1 or less are typically blank and can be deleted.

I would like to create a Macro that allows me to select the Range first (by Highlighting 1st ~ I'd like to select contiguous range of cells) and then have the macro cycle through the selected range and delete the rows that have a height of 1 or less. [so for example, if I have 100 rows in a work book and I select the rows 5 through 85 and Rows 12, 19, 33, 55 have a height less than 1, I would like to delet those rows].

Thank you

Answer
Discuss

Discussion

It sounds like you just want to delete the empty rows, so why not just have the macro do that?
don (rep: 1989) Jun 28, '16 at 1:26 pm
Some of the blank rows are apart of the formatting and need to stay, also the report has many cells that have wrapped text and Merged & Centered text.  I just need to remove, or perhaps even hide the rows that are less than a height of 1.
queue (rep: 467) Jun 28, '16 at 1:35 pm
Ok, well try the macro that I put in the answer below but remember to try this on sample data first.  I will replace the second maco in the answer with one that hides the data.
don (rep: 1989) Jun 28, '16 at 1:40 pm
Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer and then click Yes to verify it.
don (rep: 1989) Jun 29, '16 at 12:47 pm
Add to Discussion

Answers

0
Selected Answer
Sub RowHeightChecker()
Dim rnCurRow As Range, rnData As Range   'set the data area to check
Set rnData = Range("A2").CurrentRegion
'test the height of each row
For Each rnCurRow In rnData.rows        If rnCurRow.RowHeight <= 1 Then
        rnCurRow.EntireRow.Delete
    Else
    End If
Next rnCurRow
MsgBox ("Rows with row height less than/equal to 1 have been removed")   End Sub
Discuss

Discussion

Please put CODE tags around your code.
don (rep: 1989) Jun 28, '16 at 11:04 pm
Just looking at your requirement, and my suggestion above, there is a weakness in using .CurrentRegion to select your data, in that if any of the narrow rows are empty, Excel will recognise this as a limit to the current region, and not select all your data.

Your use of Selection might be better, - I just resist using .Selection, because VBA gives no suggestive help when it is used in Excel 2016
macrubco (rep: 10) Jun 29, '16 at 6:38 pm
Again sorry for delay in response (newbie).  This solution did work too! Excellent and the additional suggestion on the Current region is excellent as well.  Thank you!
queue (rep: 467) Jul 11, '16 at 10:07 am
Add to Discussion
0

If you need to remove the rows based on their height, try this macro:

Sub remove_short_cells() 
Set cell_range = Selection
For Each cell In cell_range
   If cell.Height <= 1 Then cell.EntireRow.Delete
Next cell
End Sub

To hide the short rows, try this macro:

Sub hide_short_cells() 
Set cell_range = Selection
For Each cell In cell_range
    If cell.Height <= 1 Then cell.EntireRow.Hidden = True
Next cell
End Sub

In the above macros, the 1 is the height that is checked against. If you need to adjust the height limit, just change the 1 to whatever you need.

Remember to make a backup of your file before you run a new macro on it.

Discuss

Discussion

Sorry for reponse delay...The above solution was extremely helpful and did solve the issue.  (long story, but the report I am working on comes from another party who I believe uses Crystal reports - the report section layout has various lengths which translate to the small row heights).  this solution did resolve the issue. thank you
queue (rep: 467) Jul 11, '16 at 10:06 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login