Excel loop to delete redudent rows
 

I have a macro setup to copy data based off of filtres from one work
sheet to another. Only problem being is when it copies the data over it
copies the title row over each time so i have multiple sets of titles.
This is the macro i have so far which is totaly incorrect but it shows
what i'm trying to do. Any help would be greatly appreciated. "LYR" is
text and is one of the titles.


Sub delete()
'' Section to remove redundent titles put in when copying data from
original sheet
Sheets("output").Select
Min = "a3"
Max = "a65500"
cell = Min
While cell Max
Range(cell).Select
If Selection = "LYR" Then
Selection.EntireRow.delete:=xlUp
Else
End If
cell = cell + 1
Wend
End Sub


Thanks
Andrew

Excel loop to delete redudent rows
 

Hi Andrew,

Try changing your filter copy code to exclude the header row, e.g.:

Dim rng As Range
Dim rng1 As Range


Set rng = ActiveSheet.AutoFilter.Range
Set rng1 = rng.Offset(1).Resize(rng.Rows.Count - 1)

On Error Resume Next 'In case there is no filtered data
Set rng1 = rng1.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

rng1.Copy Destination:=.Range(...)


---
Regards,
Norman



wrote in message
oups.com...
I have a macro setup to copy data based off of filtres from one work
sheet to another. Only problem being is when it copies the data over it
copies the title row over each time so i have multiple sets of titles.
This is the macro i have so far which is totaly incorrect but it shows
what i'm trying to do. Any help would be greatly appreciated. "LYR" is
text and is one of the titles.


Sub delete()
'' Section to remove redundent titles put in when copying data from
original sheet
Sheets("output").Select
Min = "a3"
Max = "a65500"
cell = Min
While cell Max
Range(cell).Select
If Selection = "LYR" Then
Selection.EntireRow.delete:=xlUp
Else
End If
cell = cell + 1
Wend
End Sub


Thanks
Andrew

Excel loop to delete redudent rows
 

Yes i know its crappy code but this is what i have to copy the data

Sheets("Data").Select
Selection.AutoFilter Field:=1, Criteria1:="1980"
Cells.Select
Selection.Copy
Selection.Copy Destination:= _
Sheets("Output").Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)

Excel loop to delete redudent rows
 

You can't add "1" to cell, because this are different types of data -
cell is range object "1" is an integer. You should try something like
that:

Sub delete()
' Section to remove redundent titles put in when copying data from
original sheet
Sheets("output").Select
For Each cell in range("A:A") 'or ("A3:A65500") if you don't want to
check first two rows
if cell.Value = "LYR" Then c.EntireRow.Delete Shift:=xlShiftUp
Next cell
End Sub

By the way, I wonder if anyone knows how to deduct range from other
range. You can add ranges (Union method) or have their intersection
(Intersect method) but I didn't find a way to do somethnig like this,
as think it should be done - Deduct(Range("A:A"),Range("A1:A2")) which
would result with the rang from above example. I'll post it on
different topic.

Excel loop to delete redudent rows
 

Sub title_delete()
' Section to remove redundent titles put in when copying data from original
Sheet
Sheets("Sheet1").Select
For r = 3 To 65500
If Cells(r, 1).Value = "LYR" Then Cells(r, 1).EntireRow.delete
If Cells(r, 1).Value = "" Then End
Next
End Sub

The second If function assumes that a blank cell in column 1 (A) means the
end of the data. This stops the code rather than running on to the end.

--
Ian
--
wrote in message
oups.com...
I have a macro setup to copy data based off of filtres from one work
sheet to another. Only problem being is when it copies the data over it
copies the title row over each time so i have multiple sets of titles.
This is the macro i have so far which is totaly incorrect but it shows
what i'm trying to do. Any help would be greatly appreciated. "LYR" is
text and is one of the titles.


Sub delete()
'' Section to remove redundent titles put in when copying data from
original sheet
Sheets("output").Select
Min = "a3"
Max = "a65500"
cell = Min
While cell Max
Range(cell).Select
If Selection = "LYR" Then
Selection.EntireRow.delete:=xlUp
Else
End If
cell = cell + 1
Wend
End Sub


Thanks
Andrew

Excel loop to delete redudent rows
 

Thanks guys,

I ended up using Ian's code because its a little cleaner but i
apreciate the help from everyone.

Andrew

Excel loop to delete redudent rows
 

You can't increment a string by a value of 1. Seperate the Cell letter &
number. This seemed to work in my test on excel 2003.

Sub delete()
Sheets("output").Select
Count = 1
MaxCount = 10
While Count < MaxCount
cell = "a" & Count
Range(cell).Select
If Selection = "LYR" Then
Selection.EntireRow.delete
Else
End If
Count = Count + 1
Wend
End Sub

" wrote:

I have a macro setup to copy data based off of filtres from one work
sheet to another. Only problem being is when it copies the data over it
copies the title row over each time so i have multiple sets of titles.
This is the macro i have so far which is totaly incorrect but it shows
what i'm trying to do. Any help would be greatly appreciated. "LYR" is
text and is one of the titles.


Sub delete()
'' Section to remove redundent titles put in when copying data from
original sheet
Sheets("output").Select
Min = "a3"
Max = "a65500"
cell = Min
While cell Max
Range(cell).Select
If Selection = "LYR" Then
Selection.EntireRow.delete:=xlUp
Else
End If
cell = cell + 1
Wend
End Sub


Thanks
Andrew