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
Hello, I need some Excel 2003 macro experts' help: I have modified a code posted in this site to automate a process I have been doing for a while. It works fine, except that in the second and third sheet it creates blank rows. The code is copied below. The 'Test1' sheet is the master sheet. Based on two conditions (columns G= Kent, Hampshire, West Sussex; and H, which is a date field (week starting monday)), the macro copies the records meeting these conditions in to the respective sheets. The issue I have is the blank rows. Could someone help me with modification of the code to remove the blank spaces please? many thanks in advance.




Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/12/2010 by Nicole
Set i = Sheets("Test1")
Set e = Sheets("Kent")
Set f = Sheets("Hants")
Set g = Sheets("W Sussex")

Dim d
Dim j
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
d = d + 1
e.Rows(d).Value = i.Rows(j).Value
ElseIf i.Range("G" & j) = "Hampshire" And i.Range("H" & j) = "06/12/2010" Then
d = d + 1
f.Rows(d).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

'
End Sub







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
0