Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.
Sub kTest()
Dim Sht1URLs, i As Long, r As Range, n As Long
Sht1URLs = Sheet1.Range("a1").CurrentRegion.Resize(, 1).Value2
Application.ScreenUpdating = 0
For i = 1 To UBound(Sht1URLs, 1)
If Len(Sht1URLs(i, 1)) Then
Set r = Sheet2.UsedRange.Cells.Find(Sht1URLs(i, 1), , , 1)
If Not r Is Nothing Then
n = n + 1
r.EntireRow.Copy Sheet3.Cells(n, 1)
Set r = Nothing
End If
End If
Next
'if you need to align the urls in first column
With Sheet3.UsedRange
On Error Resume Next
.SpecialCells(4).Delete -4159
End With
Application.ScreenUpdating = 1
End Sub
Set r = Sheet2.UsedRange.Cells.Find(Sht1URLs(i, 1), , , 1)
Set r = Sheet2.UsedRange.Cells.Find(Sht1URLs(i, 1), , , 2)
Sub kTest()
Dim Sht1URLs, i As Long, r As Range, n As Long, rngURL As Range
Set rngURL = Sheet1.Range("a1").CurrentRegion.Resize(, 1)
Sht1URLs = rngURL.Value2
Application.ScreenUpdating = 0
For i = 1 To UBound(Sht1URLs, 1)
If Len(Sht1URLs(i, 1)) Then
Set r = Sheet2.UsedRange.Cells.Find(Sht1URLs(i, 1), , , 1)
If Not r Is Nothing Then
n = n + 1
r.EntireRow.Copy Sheet3.Cells(n, 1)
Set r = Nothing
Else
rngURL.Cells(i, 1).Interior.Color = 65535
End If
End If
Next
'if you need to align the urls in first column
With Sheet3.UsedRange
On Error Resume Next
.SpecialCells(4).Delete -4159
End With
Application.ScreenUpdating = 1
End Sub
Sub kTest()
Dim Sht1URLs, i As Long, r As Range, n As Long
Dim rngURL As Range, URL As String
Set rngURL = Sheet1.Range("a1").CurrentRegion.Resize(, 1)
Sht1URLs = rngURL.Value2
Application.ScreenUpdating = 0
For i = 1 To UBound(Sht1URLs, 1)
If Len(Sht1URLs(i, 1)) Then
URL = Application.WorksheetFunction.Trim(Sht1URLs(i, 1))
Set r = Sheet2.UsedRange.Cells.Find(URL, , , 1)
If Not r Is Nothing Then
n = n + 1
r.EntireRow.Copy Sheet3.Cells(n, 1)
Set r = Nothing
Else
rngURL.Cells(i, 1).Interior.Color = 65535
End If
End If
Next
'if you need to align the urls in first column
With Sheet3.UsedRange
On Error Resume Next
.SpecialCells(4).Delete -4159
End With
Application.ScreenUpdating = 1
End Sub
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Excel VBA User Form Help | 21 | 28 | |
excel 2010 - Formatting Text in a cell to capitalize without using UPPER function | 12 | 39 | |
And OR formula | 5 | 22 | |
VBA question, how many data set exists in the sheet | 14 | 54 |
Join the community of 500,000 technology professionals and ask your questions.