I'm looking to delete rows that have no data from A106:I205. This will always
be a fixed spot.

Thanks.

See
http://www.rondebruin.nl/delete.htm

Try

Sub Loop_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = 106
Lastrow = 205

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "I"))) = 0 _
Then .Rows(Lrow).Delete
'This will delete the row if the A-I cells in the row are empty

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pgarcia" <(E-Mail Removed)> wrote in message news:5368F861-4FDA-454E-8FEA-(E-Mail Removed)...
> I'm looking to delete rows that have no data from A106:I205. This will always
> be a fixed spot.
>
> Thanks.

Sorry, then delete rows with no data in A18:G100.
Thanks

"pgarcia" wrote:

> I'm looking to delete rows that have no data from A106:I205. This will always
> be a fixed spot.
>
> Thanks.

Public Sub ProcessData()
Dim i As Long

With ActiveSheet

For i = 205 To 106 Step -1

If Application.CountA(.Cells(i, "A").Resize(, 9)) = 0 Then

.Rows(i).Delete
End If
Next i
End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"pgarcia" <(E-Mail Removed)> wrote in message
news:5368F861-4FDA-454E-8FEA-(E-Mail Removed)...
> I'm looking to delete rows that have no data from A106:I205. This will
> always
> be a fixed spot.
>
> Thanks.

maybe something like this

Sub test()

Dim c As Range
Dim rngDelete As Range

For Each c In ActiveSheet.Range("A106:A205")
If WorksheetFunction.CountA(c.Resize(1, 9)) = 0 Then
If rngDelete Is Nothing Then
Set rngDelete = c.EntireRow
Else
Set rngDelete = Union(rngDelete, c.EntireRow)
End If
End If
Next c

If Not rngDelete Is Nothing Then rngDelete.Delete


End Sub


--
Hope that helps.

Vergel Adriano


"pgarcia" wrote:

> I'm looking to delete rows that have no data from A106:I205. This will always
> be a fixed spot.
>
> Thanks.

Thanks, but it did not work.

"Ron de Bruin" wrote:

> See
> http://www.rondebruin.nl/delete.htm
>
> Try
>
> Sub Loop_Example()
> Dim Firstrow As Long
> Dim Lastrow As Long
> Dim Lrow As Long
> Dim CalcMode As Long
> Dim ViewMode As Long
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> 'We use the ActiveSheet but you can replace this with
> 'Sheets("MySheet")if you want
> With ActiveSheet
>
> 'We select the sheet so we can change the window view
> .Select
>
> 'If you are in Page Break Preview Or Page Layout view go
> 'back to normal view, we do this for speed
> ViewMode = ActiveWindow.View
> ActiveWindow.View = xlNormalView
>
> 'Turn off Page Breaks, we do this for speed
> .DisplayPageBreaks = False
>
> 'Set the first and last row to loop through
> Firstrow = 106
> Lastrow = 205
>
> 'We loop from Lastrow to Firstrow (bottom to top)
> For Lrow = Lastrow To Firstrow Step -1
>
> If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "I"))) = 0 _
> Then .Rows(Lrow).Delete
> 'This will delete the row if the A-I cells in the row are empty
>
> Next Lrow
>
> End With
>
> ActiveWindow.View = ViewMode
> With Application
> .ScreenUpdating = True
> .Calculation = CalcMode
> End With
>
> End Sub
>
>
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "pgarcia" <(E-Mail Removed)> wrote in message news:5368F861-4FDA-454E-8FEA-(E-Mail Removed)...
> > I'm looking to delete rows that have no data from A106:I205. This will always
> > be a fixed spot.
> >
> > Thanks.
>

Thanks, but that also did not work.

"Bob Phillips" wrote:

> Public Sub ProcessData()
> Dim i As Long
>
> With ActiveSheet
>
> For i = 205 To 106 Step -1
>
> If Application.CountA(.Cells(i, "A").Resize(, 9)) = 0 Then
>
> .Rows(i).Delete
> End If
> Next i
> End With
>
> End Sub
>
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
>
>
> "pgarcia" <(E-Mail Removed)> wrote in message
> news:5368F861-4FDA-454E-8FEA-(E-Mail Removed)...
> > I'm looking to delete rows that have no data from A106:I205. This will
> > always
> > be a fixed spot.
> >
> > Thanks.
>
>
>

Thanks, but that did not work. I wonder if it's my spreed sheet. I'm working
in Off 2K

"Vergel Adriano" wrote:

> maybe something like this
>
> Sub test()
>
> Dim c As Range
> Dim rngDelete As Range
>
> For Each c In ActiveSheet.Range("A106:A205")
> If WorksheetFunction.CountA(c.Resize(1, 9)) = 0 Then
> If rngDelete Is Nothing Then
> Set rngDelete = c.EntireRow
> Else
> Set rngDelete = Union(rngDelete, c.EntireRow)
> End If
> End If
> Next c
>
> If Not rngDelete Is Nothing Then rngDelete.Delete
>
>
> End Sub
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "pgarcia" wrote:
>
> > I'm looking to delete rows that have no data from A106:I205. This will always
> > be a fixed spot.
> >
> > Thanks.

Then the cells are not empty

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pgarcia" <(E-Mail Removed)> wrote in message news:9352015F-B845-44F2-97E5-(E-Mail Removed)...
> Thanks, but it did not work.
>
> "Ron de Bruin" wrote:
>
>> See
>> http://www.rondebruin.nl/delete.htm
>>
>> Try
>>
>> Sub Loop_Example()
>> Dim Firstrow As Long
>> Dim Lastrow As Long
>> Dim Lrow As Long
>> Dim CalcMode As Long
>> Dim ViewMode As Long
>>
>> With Application
>> CalcMode = .Calculation
>> .Calculation = xlCalculationManual
>> .ScreenUpdating = False
>> End With
>>
>> 'We use the ActiveSheet but you can replace this with
>> 'Sheets("MySheet")if you want
>> With ActiveSheet
>>
>> 'We select the sheet so we can change the window view
>> .Select
>>
>> 'If you are in Page Break Preview Or Page Layout view go
>> 'back to normal view, we do this for speed
>> ViewMode = ActiveWindow.View
>> ActiveWindow.View = xlNormalView
>>
>> 'Turn off Page Breaks, we do this for speed
>> .DisplayPageBreaks = False
>>
>> 'Set the first and last row to loop through
>> Firstrow = 106
>> Lastrow = 205
>>
>> 'We loop from Lastrow to Firstrow (bottom to top)
>> For Lrow = Lastrow To Firstrow Step -1
>>
>> If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "I"))) = 0 _
>> Then .Rows(Lrow).Delete
>> 'This will delete the row if the A-I cells in the row are empty
>>
>> Next Lrow
>>
>> End With
>>
>> ActiveWindow.View = ViewMode
>> With Application
>> .ScreenUpdating = True
>> .Calculation = CalcMode
>> End With
>>
>> End Sub
>>
>>
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "pgarcia" <(E-Mail Removed)> wrote in message news:5368F861-4FDA-454E-8FEA-(E-Mail Removed)...
>> > I'm looking to delete rows that have no data from A106:I205. This will always
>> > be a fixed spot.
>> >
>> > Thanks.
>>

Ok, it did work. How ever I have to high-lit the rows that have no data and
click "delete". Where is some kind of data there but it does not show. I did
find a " ' " in when of the cells, but that did not seem to be the problem.
How can I get around this? I seem that colum A does not have data.

"Vergel Adriano" wrote:

> maybe something like this
>
> Sub test()
>
> Dim c As Range
> Dim rngDelete As Range
>
> For Each c In ActiveSheet.Range("A106:A205")
> If WorksheetFunction.CountA(c.Resize(1, 9)) = 0 Then
> If rngDelete Is Nothing Then
> Set rngDelete = c.EntireRow
> Else
> Set rngDelete = Union(rngDelete, c.EntireRow)
> End If
> End If
> Next c
>
> If Not rngDelete Is Nothing Then rngDelete.Delete
>
>
> End Sub
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "pgarcia" wrote:
>
> > I'm looking to delete rows that have no data from A106:I205. This will always
> > be a fixed spot.
> >
> > Thanks.