Copy range of cells that contain only visible data

Question

On Sheet1, I have three columns that need data entered in them. The three columns will always end in the same row, but the last row will change with each set of data.

Sheet2 takes information given in the three columns on Sheet1 and applies it with formulas that put it in a certain format that I need to copy and paste to a txt file for another program.

The formulas on Sheet2 all contain IF(ISBLANK) formulas to only populate cells if there is data entered on Sheet1. If no data is entered on Sheet1, the false argument returns "" to the cell.

I want a macro or control button on Sheet1 that will copy a range of A6:B500 on Sheet2, but only if the cells are populated with the data from Sheet1, making the IF formulas true.

Here is the code of what's going on:

Sheet 1 has been explained, and contains nothing special.

Sheet 2 contains the following starting in A6 and every 5 cells down until it reaches A:496

=IF(ISBLANK(Sheet1!A2),"","host "&(INDIRECT(E6))&" {")

These formulas also continue to update the position of the cell from Sheet1. Starting in B7 through B10 there are similar formulas for more text with the cells taken from sheet 1 being updated until column B reaches B500, and those formulas are as follows:

B7:  =IF(ISBLANK(Sheet1!C2),"","hardware ethernet "&(INDIRECT(E7))&";")
B8:  =IF(ISBLANK(Sheet1!B2),"","fixed-address "&(INDIRECT(E8))&";")
B9:  =IF(ISBLANK(Sheet1!A2),"","option host-name "&""""&(INDIRECT(E9))&""";")
B10: =IF(ISBLANK(Sheet1!A2),"","}")

So far, my macro looks like this:

Sub CommandButton1_Click()
    Dim rng As Range
    If Not Selection Is Nothing Then
        Set rng = Sheets("Sheet2").Range("Sheet2!A6:Sheet2!B500").Cells.SpecialCells(xlCellTypeVisible)
        rng.Copy
    End If
End Sub

The problem I'm having is that no matter what data is entered, the macro copies all cells in the range, and I have a bunch of empty new lines after my visible data.

Not sure if I understand exactly what you're after. Part of the problem seems to be determining the last populated row. True? If so, you can use UsedRange for that purpose, e.g. maxRow = ActiveSheet.UsedRange.Rows.Count — David

NullDev NullDev · Accepted Answer · 2014-01-20T18:07:26

You should be checking for the length of the string, rather than if they are of the Visible type. Visibility is mostly associated with hidden cells. Try the following:

Sub CopyNonBlanks()
    Dim RngToCopy As Range, Cell As Range
    For Each Cell In Selection
        If Not RngToCopy Is Nothing Then
            If Len(Cell.Value) > 0 Then
                Set RngToCopy = Union(RngToCopy, Cell)
            End If
        Else
            Set RngToCopy = Cell
        End If
    Next Cell
    RngToCopy.Select 'Or .Copy
End Sub

This should highlight the cells that evaluate to "". See below screenshot:

enter image description here

Hope this helps.

Copy range of cells that contain only visible data

1 Answers