Copying non-blank rows

Mr2017

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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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
472
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
472
I worked it out myself.
 

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
472
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,759
Messages
5,488,683
Members
407,651
Latest member
Halosty

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top