98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Macros
Macros
Resolved · Medium Priority · Version 2010
RE: Macros
Hi Rachael
Thanks for your question.
Yes you can use a macro to delete blank rows. You will have to write the code rather than record it. Here is an example where the data is in a tabular format starting in cell A1.
Sub DelBlanks()
Range("A10000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Do Until ActiveCell = Range("A1")
If ActiveCell = "" Then
Selection.Delete Shift:=xlUp
End If
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Loop
End Sub
Notes:
If your data contains more than 10000 rows then adjust the first line to go below the last row.
If your data starts in a cell another cell such as C1 then replace A10000 with C10000 and A1 with C1.
You also can delete blank rows without a macro by using Autofilter provided the data is in a tabular format.
Follow these steps
1. Select all columns of your data
2. Select Data, Filter
3. In any column untick blanks
4. Copy and paste the selection to a new worksheet or to a blank cell to the right.
The pasted data will be without the blanks.
You could create this process as a macro. This code copies the filtered data to a new worksheet.
Sub RemoveBlanks()
Columns("A:D").Select
Selection.AutoFilter
ActiveSheet.Range("A:D").AutoFilter Field:=1, Criteria1:="<>"
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub
Notes:
Adjust the first line A:D to suit your data.
The macro copies the formatting as well but does not include the blank rows.
Hope either of these methods help.
Regards
Doug
STL
Tue 15 Nov 2016: Automatically marked as resolved.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:DATEDIF functionThe DATEDIF function is a worksheet function that computes the difference between two dates. This function is documented only in the help files for Excel 2000. It isn't documented in Excel 2002. |