Copying non-blank rows

Mr2017

Active Member
Hi

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?

ABCDEFG
11111
11111
11111
11111
22222
22222
22222
33333
33333

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

Mr2017

Active Member
Hi My Aswer Is This

Sure.

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.

Thanks


Code:
Sub DeleteBlankRows()


'copy and paste the formulas in column A as values


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


'delete the blank rows in column B


Range("B:B").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete


'copy the data


Range("A:G").Copy


End Sub
 

My Aswer Is This

Well-known Member
Thanks for sharing that.

You can also do it this way:
Code:
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:

Code:
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)
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

This Week's Hot Topics

  • 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...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • 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...
  • 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#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top