Copying non-blank rows

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
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>
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
You asked that the row be copied. But did not say where to copy it to.
 

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
Hi My Aswer Is This

I'd want to copy the data to either a new sheet or new workbook, but I've managed to work this out.

Thanks for your response, though.


 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
I hope you figured it out on your own and did not get the results from another online help forum.
Hi My Aswer Is This

I'd want to copy the data to either a new sheet or new workbook, but I've managed to work this out.

Thanks for your response, though.


 

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
I worked it out myself.
 

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
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
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,017
Messages
5,484,214
Members
407,434
Latest member
huynnguyen

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top