select row if cell in column h is NOT blank

[rburnet4]

I'm trying to come up with a marco solution that will select rows if the cell in column H is NOT BLANK.

From there I think it will be rather easy to cut these rows and paste them in another workbook (that's filename does not change) at the end of the worksheet (in the first row that is completely blank).

Both workbooks only have one worksheet in them. Let me know what you think.
Thanks for all your help!!

 

[Legacy 14611]

Try

Sub xyz()
With Range("H:H")
    Union(.SpecialCells(2), .SpecialCells(-4123)).EntireRow.Select
End With
End Sub

Although your approach seems a rather inefficient way to achieve what you seem to want.

 

[machopicho]

This macro allows to you to select rows corresponding to cells in column H not blanks:

Sub test()
nr = Cells(Rows.Count, 8).End(xlUp).Row
For r = 1 To nr
  If Cells(r, 8) <> "" Then
     Rng = Rng & r & ":" & r & ", "
  End If
Next
rnge = Mid(Rng, 1, Len(Rng) - 2)
Range(rnge).Select
End Sub

 

[Legacy 14611]

The above code of mine works only if there are formulae in one or more of the cells in ColH.

If you only have values or blanks in ColH then this simpler version may do what you want

Sub xyz2()
Range("H:H").SpecialCells(2).EntireRow.Select
End Sub

 

[machopicho]

The above code of mine works only if there are formulae in one or more of the cells in ColH.

If you only have values or blanks in ColH then this simpler version may do what you want

Sub xyz2()
Range("H:H").SpecialCells(2).EntireRow.Select
End Sub

Hi rugila...
in argument of specialcells there is a number that correspondind to xlCellTypeConstants
Can you tell me other numbers with their correspondance??
Thanks

 

[Armando Montes]

See here:
http://www.xcelfiles.com/XL972000C.html

 

[lenze]

Am I missing something? Can't you just AutoFilter (either manually or via code) and copy the results to the other workbook?
lenze

 

[Legacy 14611]

Hi rugila...
in argument of specialcells there is a number that correspondind to xlCellTypeConstants
Can you tell me other numbers with their correspondance??
Thanks

A convenient reference, at least for XL2003, is http://msdn.microsoft.com/en-us/library/aa213567(office.11).aspx
There are some valid specialcell numbers which this doesn't include though, like SpecialCells(8), whch you may need to experiment with a bit to see just what it means. I don't think it's got a convenient verbal summary like CellTypeBlanks, etc.

 

[Legacy 14611]

Am I missing something? Can't you just AutoFilter (either manually or via code) and copy the results to the other workbook?
lenze

I think OP would probably welcome details on this or other of the several efficient means of solving his/her overall problem.

So if you'd like to detail your approach in the specific context of OP's problem I'm sure it would be welcomed.

For my part I was solely considering the narrower question (as OP specifically asked) of selecting only those entire rows up to the last used row that don't have blanks in ColumnH. Just seemed like a problem of some interest in its own right.

 

[rburnet4]

This macro allows to you to select rows corresponding to cells in column H not blanks:

Sub test()
nr = Cells(Rows.Count, 8).End(xlUp).Row
For r = 1 To nr
  If Cells(r, 8) <> "" Then
     Rng = Rng & r & ":" & r & ", "
  End If
Next
rnge = Mid(Rng, 1, Len(Rng) - 2)
Range(rnge).Select
End Sub

This is perfect. One thing I did not think of though... How do I make sure the Macro does not pick up the first two rows (ie headers)?