Heather515
New Member
- Joined
- Sep 11, 2021
- Messages
- 22
- Office Version
- 365
- Platform
- Windows
Data.xlsx | |||
---|---|---|---|
D | |||
6 | |||
Sheet1 |
I need help with making my code more efficient. As you can see, I want to code "Apple" as "1", and everything else as "0". However, I want to leave other cells blank when the cells in the first column is also blank. My current code first return 1 for Apple and 0 for others (including blank cells), and then I run the loop to remove 0 generated from the first step. It takes too long to remove the unwanted zeros since I have hundreds of thousands rows. I also need to keep the blank rows for merging data later.
Could you please help change my code or write a new code so cells with "Apple" return value of "1" and only other non-blank cells return value of "0" so I don't need to spend extra time to remove the unwanted zeros. Thanks!
Sub Apple()
Dim LRow As Long
LRow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To LRow
If InStr(Cells(x, 1), "Apple") > 0 Then
Cells(x, 2) = "1"
Else
Cells(x, 2) = "0"
End If
Next x
For x = 2 To LRow
If IsEmpty(Cells(x, 1)) Then
Cells(x, 2) = ""
End If
Next x
End Sub