Delete row with no data
 

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

Thanks.

Delete row with no data
 

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" wrote in message ...
I'm looking to delete rows that have no data from A106:I205. This will always
be a fixed spot.

Thanks.

Delete row with no data
 

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.

Delete row with no data
 

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" wrote in message
...
I'm looking to delete rows that have no data from A106:I205. This will
always
be a fixed spot.

Thanks.

Delete row with no data
 

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.

Delete row with no data
 

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" wrote in message ...
I'm looking to delete rows that have no data from A106:I205. This will always
be a fixed spot.

Thanks.

Delete row with no data
 

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" wrote in message
...
I'm looking to delete rows that have no data from A106:I205. This will
always
be a fixed spot.

Thanks.

Delete row with no data
 

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.

Delete row with no data
 

Then the cells are not empty

--

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


"pgarcia" wrote in message ...
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" wrote in message ...
I'm looking to delete rows that have no data from A106:I205. This will always
be a fixed spot.

Thanks.

Delete row with no data
 

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.

Delete row with no data
 

Is column A is empty see

Sub DeleteBlankRows_2()
'This macro delete all rows with a blank cell in column A
'If there are no blanks or there are too many areas you see a MsgBox
Dim CCount As Long
On Error Resume Next

With Columns("A") ' You can also use a range like this Range("A1:A8000")
CCount = .SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Cou nt
If CCount = 0 Then
MsgBox "There are no blank cells"
ElseIf CCount = .Cells.Count Then
MsgBox "There are more then 8192 areas"
Else
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
End With

On Error GoTo 0
End Sub

--

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


"pgarcia" wrote in message ...
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.