macros

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Macros

Macros

resolvedResolved · Medium Priority · Version 2010

Rachael has attended:
Excel Advanced course

Macros

Creating a macro to delete empty rows.

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 courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

DATEDIF function

The 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.
the formula function is:
=DATEDIF(Your age,Today(),"Y")
Y stands for year

View all Excel hints and tips


Server loaded in 0.07 secs.