Copy all cells in column A;C that have a value in either B or C

[Varr86]

Hi,

I want to copy all the cells in column A;C. Requirement is that A is not blank, and either B or C is not blank.

For instance, I copy the following list:

[TABLE="width: 192"]
 <colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="width: 64"]test1[/TD]
  [TD="width: 64, align: right"]10[/TD]
  [TD="width: 64, align: right"]10[/TD]
 [/TR]
 [TR]
  [TD]test2[/TD]
  [TD="align: right"]20[/TD]
  [TD="align: right"]20[/TD]
 [/TR]
 [TR]
  [TD]test3[/TD]
  [TD="align: right"]30[/TD]
  [TD="align: right"]30[/TD]
 [/TR]
 [TR]
  [TD]test4[/TD]
  [TD="align: right"]40[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]test5[/TD]
  [TD="align: right"]50[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]test6[/TD]
  [TD="align: right"]60[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]test7[/TD]
  [TD="align: right"]70[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]test8[/TD]
  [TD="class: xl63"] [/TD]
  [TD="class: xl63"] [/TD]
 [/TR]
 [TR]
  [TD]test9[/TD]
  [TD][/TD]
  [TD="align: right"]10[/TD]
 [/TR]
 [TR]
  [TD]test10[/TD]
  [TD][/TD]
  [TD="align: right"]20[/TD]
 [/TR]
 [TR]
  [TD]test11[/TD]
  [TD][/TD]
  [TD="align: right"]30[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]test12[/TD]
  [TD="class: xl63"] [/TD]
  [TD="class: xl63"] [/TD]
 [/TR]
 [TR]
  [TD]test13[/TD]
  [TD="align: right"]30[/TD]
  [TD][/TD]
[/TR]
</tbody>[/TABLE]

Then the output should be:

[TABLE="width: 192"]
 <colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="width: 64"]test1[/TD]
  [TD="width: 64, align: right"]10[/TD]
  [TD="width: 64, align: right"]10[/TD]
 [/TR]
 [TR]
  [TD]test2[/TD]
  [TD="align: right"]20[/TD]
  [TD="align: right"]20[/TD]
 [/TR]
 [TR]
  [TD]test3[/TD]
  [TD="align: right"]30[/TD]
  [TD="align: right"]30[/TD]
 [/TR]
 [TR]
  [TD]test4[/TD]
  [TD="align: right"]40[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]test5[/TD]
  [TD="align: right"]50[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]test6[/TD]
  [TD="align: right"]60[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]test7[/TD]
  [TD="align: right"]70[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]test9[/TD]
  [TD][/TD]
  [TD="align: right"]10[/TD]
 [/TR]
 [TR]
  [TD]test10[/TD]
  [TD][/TD]
  [TD="align: right"]20[/TD]
 [/TR]
 [TR]
  [TD]test11[/TD]
  [TD][/TD]
  [TD="align: right"]30[/TD]
 [/TR]
 [TR]
  [TD]test13[/TD]
  [TD="align: right"]30[/TD]
  [TD][/TD]
[/TR]
</tbody>[/TABLE]

Removing the row of test 8 & test 12, because both have B & C blank. Using Excel 2013.

 

[Scrappy666]

In cell D2 you could try -

=if(and(A2<>"",B2="",C2=""),1,0)

Filtering on the 0's should then give you the list you want, ready for copying.

Thanks,
Dan.

 

[Varr86]

Thanks Dan. Is there any way to do these 2 steps in 1 automatically through VBA? I'd rather have the user press a button that automatically copy's the right cells than filter and copy the list manually.

 

[Ranitha]

Try using this code to automate the above activity.

Sub RemoveBlanks()
Dim nm(100) As String
Dim a(100), b(100) As String


Range("A1").Activate
n = ActiveCell.End(xlDown).Row


For i = 1 To n
nm(i) = ActiveCell.Value
a(i) = ActiveCell.Offset(0, 1).Value
b(i) = ActiveCell.Offset(0, 2).Value
ActiveCell.Offset(1, 0).Activate
Next




Range("H1").Activate
For j = 1 To n
If a(j) <> "" Or b(j) <> "" Then
ActiveCell.Value = nm(j)
ActiveCell.Offset(0, 1).Value = a(j)
ActiveCell.Offset(0, 2).Value = b(j)
ActiveCell.Offset(1, 0).Activate
End If


Next




End Sub

 

[Varr86]

This works great! I might be asking for much, but it's a bit slow when pasting the values. As I will do this for about 100.000 rows, is there anyway to speed up the proces?

 

[Ranitha]

Try this code. It will be little faster.
I have done small modifications to the code. Declared all the variables, also included below 2 lines also
Application.DisplayAlerts = FalseApplication.ScreenUpdating = False

Sub RemoveBlanks1()
Dim nm(10000) As String
Dim a(10000), b(10000) As String
Dim n, i, j, c As Integer


Application.DisplayAlerts = False
Application.ScreenUpdating = False


Range("A1").Activate
n = ActiveCell.End(xlDown).Row


For i = 1 To n
nm(i) = Cells(i, 1).Value
a(i) = Cells(i, 2).Value
b(i) = Cells(i, 3).Value
Next


Range("H1").Activate
For j = 1 To n
If a(j) <> "" Or b(j) <> "" Then
ActiveCell.Value = nm(j)
ActiveCell.Offset(0, 1).Value = a(j)
ActiveCell.Offset(0, 2).Value = b(j)
ActiveCell.Offset(1, 0).Activate
End If
Next
End Sub

 

[Varr86]

Try this code. It will be little faster.
I have done small modifications to the code. Declared all the variables, also included below 2 lines also
Application.DisplayAlerts = FalseApplication.ScreenUpdating = False

Sub RemoveBlanks1()
Dim nm(10000) As String
Dim a(10000), b(10000) As String
Dim n, i, j, c As Integer


Application.DisplayAlerts = False
Application.ScreenUpdating = False


Range("A1").Activate
n = ActiveCell.End(xlDown).Row


For i = 1 To n
nm(i) = Cells(i, 1).Value
a(i) = Cells(i, 2).Value
b(i) = Cells(i, 3).Value
Next


Range("H1").Activate
For j = 1 To n
If a(j) <> "" Or b(j) <> "" Then
ActiveCell.Value = nm(j)
ActiveCell.Offset(0, 1).Value = a(j)
ActiveCell.Offset(0, 2).Value = b(j)
ActiveCell.Offset(1, 0).Activate
End If
Next
End Sub

Thanks! I just tested it with 10.000 rows and it took nearly 2 hours to complete. It seems like a relatively simple check, so I thought this would be done in seconds. Would it be faster if combined with Dan's formula in columns C? So the VBA only has to check whether that column does not contain 0?