Selection of rows based on conditions
Closed
thomasngri
Posts
1
Registration date
Wednesday December 8, 2010
Status
Member
Last seen
December 8, 2010
-
Dec 8, 2010 at 04:58 AM
Helper - Dec 17, 2010 at 10:22 PM
Helper - Dec 17, 2010 at 10:22 PM
Related:
- Selection of rows based on conditions
- How to take screenshot of selected area in excel - Guide
- We couldn't find an account matching the login info you entered, but found an account that closely matches based on your login history ✓ - Facebook Forum
- How to insert rows in excel automatically based on cell value ✓ - Excel Forum
- Multiple selection photoshop - Guide
- Auto serial number in excel based on another column ✓ - Excel Forum
1 response
Because you are trying to use the same variable across three sheets, Excel is "confused." Try using a different variable for each sheet so the list will be created without blank rows. I modified the code.
Hope this helps.
Set i = Sheets("Test1")
Set e = Sheets("Kent")
Set f = Sheets("Hants")
Set g = Sheets("W Sussex")
Dim b
Dim c
Dim d
Dim j
b = 1
c = 1
d = 1
j = 2
Do Until IsEmpty(i.Range("G" & j))
If i.Range("G" & j) = "Kent" And i.Range("H" & j) = "06/12/2010" Then
b = b + 1
e.Rows(b).Value = i.Rows(j).Value
ElseIf i.Range("G" & j) = "Hampshire" And i.Range("H" & j) = "06/12/2010" Then
c = c + 1
f.Rows(c).Value = i.Rows(j).Value
ElseIf i.Range("G" & j) = "West Sussex" And i.Range("H" & j) = "06/12/2010" Then
d = d + 1
g.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1
Loop
Hope this helps.
Set i = Sheets("Test1")
Set e = Sheets("Kent")
Set f = Sheets("Hants")
Set g = Sheets("W Sussex")
Dim b
Dim c
Dim d
Dim j
b = 1
c = 1
d = 1
j = 2
Do Until IsEmpty(i.Range("G" & j))
If i.Range("G" & j) = "Kent" And i.Range("H" & j) = "06/12/2010" Then
b = b + 1
e.Rows(b).Value = i.Rows(j).Value
ElseIf i.Range("G" & j) = "Hampshire" And i.Range("H" & j) = "06/12/2010" Then
c = c + 1
f.Rows(c).Value = i.Rows(j).Value
ElseIf i.Range("G" & j) = "West Sussex" And i.Range("H" & j) = "06/12/2010" Then
d = d + 1
g.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1
Loop