• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy data to another sheet skipping blank cell with condition

aamirsq

Member
Sub Copy_Stuff1()

Dim cell As Range, rr As Long

rr = 2

Application.ScreenUpdating = False

Sheets("Sheet4").Range("A2:A3000").ClearContents

For Each cell In Sheets("dps").Range("AHR282:ALF388 ")

If Len(cell) > 0 Then

Sheets("Sheet4").Range("A" & rr).Value = cell.Value

rr = rr + 1

End If

Next cell

Application.ScreenUpdating = True

End Sub


How can i add additional condition for not selecting numbers (or only selecting text data) from dps sheet ?
 
Hi Aamir,


try below..

[pre]
Code:
Sub Copy_Stuff1()
Dim cell As Range, rr As Long
rr = 2
Application.ScreenUpdating = False
Sheets("Sheet4").Range("A2:A3000").ClearContents
For Each cell In Sheets("dps").Range("AHR282:ALF388 ")
If Not IsNumeric(cell.Value) And Len(cell.Value) > 0 Then
Sheets("Sheet4").Range("A" & rr).Value = cell.Value
rr = rr + 1
End If
Next cell
Application.ScreenUpdating = True
End Sub
[/pre]

Regards,

=DEC2HEX(3563)
 
Thanks it works.


Can we add 1 more thing in it, i have a date in dps sheet (AHR6:ALF6) and the when all items (column A) are transfer to sheet4 i want in column B their relevant date with them.


Is it possible?
 
Hi Aamir,


Sorry for late response.. :(


Try below..

[pre]
Code:
Sub Copy_Stuff1()
Dim cell As Range, rr As Long
rr = 2
Application.ScreenUpdating = False
Sheets("Sheet4").Range("A2:B3000").ClearContents
For Each cell In Sheets("dps").Range("AHR282:ALF388 ")
If Not IsNumeric(cell.Value) And Len(cell.Value) > 0 Then
Sheets("Sheet4").Range("A" & rr).Value = cell.Value
Sheets("Sheet4").Range("B" & rr).Value = Sheets("dps").Cells(6, cell.Column)
rr = rr + 1
End If
Next cell
Application.ScreenUpdating = True
End Sub
[/pre]
 
thanks for the help, i was trying with the index & match but when there was same date in one row then it was returning wrong date :(, as it was choosing the 1st one only.


Here is FYI index match formula, in this row it started error results;

INDEX(DPS!$AHR$6:$ALF$6,IFERROR(MATCH(A41,DPS!$AHR$282:$ALF$282,0),IFERROR(MATCH(A41,DPS!$AHR$283:$ALF$283,0),IFERROR(MATCH(A41,DPS!$AHR$285:$ALF$285,0),MATCH(A41,DPS!$AHR$288:$ALF$288,0)))))


btw this formula gets complex as we go proceed but vb squeeze it down to ONLY 1 line.
 
wow i got into another situation now, there is a column C282:C388 can i search through this & match in sheet4 at column C?


FYI, this column has text properties, some cell are empties, #s, text all are mix & i need to link.
 
Back
Top