#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default macro to delete rows

I have been away from writing macros for a while now, so any help would be
greatly appreciated. I'm attempting to write a simple macro to delete lines
in a spreadsheet based on cell values in a particular row / column. I know
it's simple but again, it's been awhile!

Thanks in advance!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default macro to delete rows

You gave us spare detail but here is a subroutine that deletes a row when
the value in column H (the 8th column) has a value less than 10. It does
this for rows 1 to 24.

Sub tryme()
For j = 1 To 24
If Cells(j, 8) < 10 Then
Cells(j, 8).EntireRow.Delete
End If
Next j
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Aceensor" wrote in message
...
I have been away from writing macros for a while now, so any help would be
greatly appreciated. I'm attempting to write a simple macro to delete
lines
in a spreadsheet based on cell values in a particular row / column. I
know
it's simple but again, it's been awhile!

Thanks in advance!



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default macro to delete rows

Hi,

You should be able to adapt this. It currently looks for 'somevalue' in
column A and if it finds it adds the entirerow to a range that is deleted at
the end of the sub.

Sub Stantial()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = "Somevalue" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.delete
End If
End Sub

Mike

"Aceensor" wrote:

I have been away from writing macros for a while now, so any help would be
greatly appreciated. I'm attempting to write a simple macro to delete lines
in a spreadsheet based on cell values in a particular row / column. I know
it's simple but again, it's been awhile!

Thanks in advance!

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default macro to delete rows

I bet you meant

For j = 24 To 1 step -1

Mike

"Bernard Liengme" wrote:

You gave us spare detail but here is a subroutine that deletes a row when
the value in column H (the 8th column) has a value less than 10. It does
this for rows 1 to 24.

Sub tryme()
For j = 1 To 24
If Cells(j, 8) < 10 Then
Cells(j, 8).EntireRow.Delete
End If
Next j
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Aceensor" wrote in message
...
I have been away from writing macros for a while now, so any help would be
greatly appreciated. I'm attempting to write a simple macro to delete
lines
in a spreadsheet based on cell values in a particular row / column. I
know
it's simple but again, it's been awhile!

Thanks in advance!




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default macro to delete rows

Hi Mike,

This is exactly what I needed - thanks so much!

"Mike H" wrote:

Hi,

You should be able to adapt this. It currently looks for 'somevalue' in
column A and if it finds it adds the entirerow to a range that is deleted at
the end of the sub.

Sub Stantial()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = "Somevalue" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.delete
End If
End Sub

Mike

"Aceensor" wrote:

I have been away from writing macros for a while now, so any help would be
greatly appreciated. I'm attempting to write a simple macro to delete lines
in a spreadsheet based on cell values in a particular row / column. I know
it's simple but again, it's been awhile!

Thanks in advance!



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default macro to delete rows

Why?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Mike H" wrote in message
...
I bet you meant

For j = 24 To 1 step -1

Mike

"Bernard Liengme" wrote:

You gave us spare detail but here is a subroutine that deletes a row when
the value in column H (the 8th column) has a value less than 10. It does
this for rows 1 to 24.

Sub tryme()
For j = 1 To 24
If Cells(j, 8) < 10 Then
Cells(j, 8).EntireRow.Delete
End If
Next j
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Aceensor" wrote in message
...
I have been away from writing macros for a while now, so any help would
be
greatly appreciated. I'm attempting to write a simple macro to delete
lines
in a spreadsheet based on cell values in a particular row / column. I
know
it's simple but again, it's been awhile!

Thanks in advance!






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to delete rows Hankjam[_2_] Excel Discussion (Misc queries) 4 October 1st 08 03:58 PM
Need a macro to delete rows jmr4h8 Excel Discussion (Misc queries) 9 July 2nd 08 11:16 PM
My Macro Won't Delete Rows?? VexedFist New Users to Excel 3 April 16th 07 04:14 PM
delete rows using macro nospam Excel Worksheet Functions 5 December 20th 06 02:26 PM
delete rows-macro TUNGANA KURMA RAJU Excel Discussion (Misc queries) 5 January 13th 06 01:01 PM


All times are GMT +1. The time now is 09:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"