Macro that identifies non blank values on column and create new column on another tab with no empty values


New Member
Jan 9, 2018

I'm new to VBA and I would like some help.

I have a huge table and I'm interested in values on columns A, H and I. What I would like to do is to identify for each row if the cells on columns H and I are empty or not. If either cells on column H or cells on column I on a specific row are not empty, I want to get these values (along with values displayed on column A) and paste them in a table in another tab. If both cells on column H and column I on a row are empty, then I dont want to get any value of this table.

Can someone help me with this?

Thank you


Well-known Member
Apr 21, 2004
something along the lines of

Dim c As Range, rng
Dim lr As Long
Dim a_val As String
Dim h_val As String
Dim i_val As String

lr = Range("h" & Rows.Count).End(xlUp).Row
Set rng = Range("h1:h" & lr)
For Each c In rng
If c.Value <> "" Or c.Offset(0, 1).Value <> "" Then
a_val = c.Offset(0, -7).Value
h_val = c.Value
i_val = c.Offset(0, 1).Value
lr = Range("a" & Rows.Count).End(xlUp).Row + 1
Range("a" & lr) = a_val
Range("h" & lr) = h_val
Range("i" & lr) = i_val
End If
Next c


Active Member
Oct 30, 2015
Well since I was developing at the same time I'll go ahead and publish my code as well

Option Explicit

Sub copyAHI()
Dim i As Long
Dim lastrow As Long
Dim nextrow As Long
Dim cfws As Worksheet
Dim ctws As Worksheet
Dim val As Boolean

Set cfws = Sheet1
Set ctws = Sheet2

lastrow = cfws.Cells(cfws.Rows.Count, "A").End(xlUp).Row
nextrow = ctws.Cells(ctws.Rows.Count, "A").End(xlUp).Row + 1
val = False

For i = 2 To lastrow
    If Not IsEmpty(cfws.Range("H" & i)) Then
        val = True
    ElseIf Not IsEmpty(cfws.Range("I" & i)) Then
        val = True
    End If
    If val = True Then
        ctws.Cells(nextrow, 1).Value = cfws.Cells(i, 1).Value
        ctws.Cells(nextrow, 2).Value = cfws.Cells(i, 8).Value
        ctws.Cells(nextrow, 3).Value = cfws.Cells(i, 9).Value
        nextrow = nextrow + 1
    End If
    val = False
Next i

End Sub

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...