P
pgarcia
I'm looking to delete rows that have no data from A106:I205. This will always
be a fixed spot.
Thanks.
be a fixed spot.
Thanks.
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Ron de Bruin
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
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
P
pgarcia
Sorry, then delete rows with no data in A18:G100.
Thanks
Thanks
B
Bob Phillips
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)
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)
V
Vergel Adriano
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
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
P
pgarcia
Thanks, but it did not work.
Ron de Bruin said: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
P
pgarcia
Thanks, but that also did not work.
Bob Phillips said: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)
P
pgarcia
Thanks, but that did not work. I wonder if it's my spreed sheet. I'm working
in Off 2K
in Off 2K
R
Ron de Bruin
Then the cells are not empty
P
pgarcia
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.
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.
R
Ron de Bruin
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.Count
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
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.Count
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
P
pgarcia
Ok, that also did not work. But I see that there is a problem with my sheet.
I have a fomula in those cells,
=IF(ISERROR(INDEX(Warning!A:A,Betsy!$K19+1)),"1",INDEX(Warning!A:A,Betsy!$K19+1)),
then I cut and paste special value do it would seem that there is not data.
But there is some thing there. When I high-lit the cells and delete, all the
VB codes run as they should. Is this a Off2k problem?
I have a fomula in those cells,
=IF(ISERROR(INDEX(Warning!A:A,Betsy!$K19+1)),"1",INDEX(Warning!A:A,Betsy!$K19+1)),
then I cut and paste special value do it would seem that there is not data.
But there is some thing there. When I high-lit the cells and delete, all the
VB codes run as they should. Is this a Off2k problem?
R
Ron de Bruin
I have a fomula in those cells,
Then the cells are not empty
I think you have this also in "" in the formula
Excel still thinks that there is something in the cell after you do a pastespecial
You can loop the list and test for ""
There are examples below my loop macro that you can use
http://www.rondebruin.nl/delete.htm
Then the cells are not empty
I think you have this also in "" in the formula
Excel still thinks that there is something in the cell after you do a pastespecial
You can loop the list and test for ""
There are examples below my loop macro that you can use
http://www.rondebruin.nl/delete.htm
P
pgarcia
Great, that was it! I'm going to post the VB code incase anyone elso should
need it.
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 = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'We check the values in the A column in this example
With .Cells(Lrow, "A")
If Not IsError(.Value) Then
If .Value = "" Then .EntireRow.Delete
'This will delete each row with the Value ""
'in Column A, case sensitive.
End If
End With
Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
need it.
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 = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'We check the values in the A column in this example
With .Cells(Lrow, "A")
If Not IsError(.Value) Then
If .Value = "" Then .EntireRow.Delete
'This will delete each row with the Value ""
'in Column A, case sensitive.
End If
End With
Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.