CODEWITHSUNDEEP
excelvba
Mohamad Faisal
Mohamad Faisal

Reputation: 81

Delete empty value in range-loop

i want to filter some data from many single table into 1 table. The problem start as i want to show only some data instead all values read from looping.eg; I have many data in column A. But I only want to select few data only, and this data has fix increment such as at column A5,A10,A15 and etc.

I have try to use if-statement to select only when there is value but not working, as it will paste all value instead.

Sub transpose1()

'***Define Variable
Dim Strings() As Variant
Dim n As Long
Dim LastRowData As Long
Dim LastRow As Long

'***Read Data
Sheets("Results").Activate
LastRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
LastRowData = LastRow

'***Redefine Array
ReDim Strings(LastRowData)

'***Read Data Variable
For n = 2 To LastRowData 'loop strings data
    Strings(n) = Sheets("Results").Cells(1 + n, 1)
    n = n + 12 'data increment at fix size
Next n

'***Write data
If IsEmpty(n) = False Then 'select only n with value
    For n = 0 To LastRowData
            Sheets("Test Transpose").Cells(1 + n, 1) = Strings(n) ' paste value when only n has value only
    Next n
End If

End Sub

enter image description here

Upvotes: 0

Views: 48

Answers (1)

Alex de Jong
Alex de Jong

Reputation: 1267

Well n is a long variable and that is not empty. You should check if strings(n) is empty:

If Isempty(strings(n)) = False Then

Also you have to place this if statement inside the for-loop, otherwise it will just check the same element everytime:

For n = 0 to LastRowData    
    If Isempty(strings(n)) = False Then
          Sheets("Test Transpose").Cells(1 + n, 1) = Strings(n) ' paste value when only n has value only    
    End if  
Next n

Upvotes: 1

Related Questions