VBA Current Region With Value Not Formula

[jusho]

I have a sheet that contain data from A1:E10. In A2 to A10 i have a formula =if(B2="","",A1+1). So if there is no data in row 2 so on there will not seen the number in column A. Then i try to copy currentregion using vba to select all cell with data but it always select all A1:E10 its because there is formula in column A. Any way to select only row with value maybe from A1:E5. Thanks.

 

[Gavin T]

Use the special cells method:

Range("A1:E5").SpecialCells(xlCellTypeFormuals, 1).Select

 

[jusho]

Use the special cells method:

Range("A1:E5").SpecialCells(xlCellTypeFormuals, 1).Select

Here my case. In column A3 till A10 there a formula =if(b3="","",a2+1). So if there is no data in column B3, the number in A3 wil not show. But actually there is formula inside. The problem when i currentregion it select all A1:E10, i need the current region only select the cell that contain data not formula, so the result it will select A1:E5. So i dont want put static range in vba.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]No[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Teeroy]

Try the following NotBlank Function (with a sub to show its usage):

Sub test()
Dim C00 As Range
Set C00 = NotBlank(Range("A1").CurrentRegion)
Debug.Print C00.Address
End Sub


Function NotBlank(Rng As Range) As Range
Dim C01 as Range
Set NotBlank = Nothing
For Each C01 In Rng
    If C01.Value <> "" Then
        If NotBlank Is Nothing Then
            Set NotBlank = C01
        Else
            Set NotBlank = Application.Union(NotBlank, C01)
        End If
    End If
Next
End Function

 

[Peter_SSs]

See if this helps

Sub GetRange()
  Dim lr As Long
 
  lr = Columns("A").Find(What:="*", After:=Range("A1"), LookIn:=xlValues, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  Range("A1:E" & lr).Select
End Sub

 

[jusho]

I will try it tommorow at work. Tx for reply.

 

[jusho]

Thanks its work