Copying non-blank rows


Active Member

I'd like to copy all non-blank rows below a certain row.

In the sample data below, the data starts in row 5 and goes up to column G.

I'd like to copy a whole row IF the data in the first or second column of that row is populated.

So if it was row 5, the row would only be copied IF there was a value in cell A5 or B5.....

Also, the data is not contiguous, so there are some columns which are blank (column C and F in this example), but I would still like to copy the whole row if the cells in the first or second column of that row aren't blank.

Has anyone done this before, please?


<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>


Active Member
Hi My Aswer Is This


In the real data that I have, I noticed that I could just use one column instead of two to decide whether I wanted to delete the blank rows.

So using the sample data provided above, I deleted all blank rows in column B.

I also had to copy and paste the formulas in column A as values, as when copying the data over to a new workbook, some of the formulas gave errors.

I'd originally tried to loop through the data and copy all rows that were NOT blank using code I found on another site, but that took forever! Nearly 20mins, as there was lots of data!

So deleting the blank rows based on a specific column is a MUCH QUICKER solution!

Below is the code I used (modified to fit the sample data provided above).

If you have any other questions about it, please let me know.


Sub DeleteBlankRows()

'copy and paste the formulas in column A as values

Range("A:A").EntireColumn.PasteSpecial xlPasteValues

'delete the blank rows in column B


'copy the data


End Sub

My Aswer Is This

Well-known Member
Thanks for sharing that.

You can also do it this way:
Sub Convert_Column_Formulas_To_Values()
'Modified 7/22/2019 6:40:39 AM  EDT
Range("A:A").Value = Range("A:A").Value
End Sub

My Aswer Is This

Well-known Member
If you want to copy all the non blank rows by using column B to see if row is empty
Try this script:

It will copy the non blank rows to a sheet named "Summary" Row(4)
This is using Filter which is very quick:

Sub Filter_Me_Please()
'Modified  7/22/2019  7:10:14 AM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim c As Long
Dim s As Variant
c = 2 ' Column Number Modify this to your need
s = "<>" & "" 'Search Value Modify to your need
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
Dim Counter As Long
With ActiveSheet.Cells(1, c).Resize(Lastrow)
    .AutoFilter 1, s
    Counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If Counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Summary").Rows(4)
        MsgBox "No values found"
    End If
End With
Application.ScreenUpdating = True
End Sub

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...