Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing unnecessary rows through macro
I have data in column E which consists of names. some rows have data
(names) and some does not (blank). I want to delete all rows where column e has blank cells and where the data = "header". For instance, lets say the column E looks like this (Range E1:E23) header name1 name2 name3 header name4 name5 name6 header name7. The desired output i want is like this (Range E1:E7) name1 name2 name3 name4 name5 name6 name7 The last row is unknown, we therefore need to identify the last row first and then run the macro Can somebody help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing unnecessary rows through macro
Hi,
This looks for the value "Header" in e1 and if it finds it deletes blank rows. Right click your sheet tab, view code and paste this and run it. Sub marine() Dim MyRange As Range, MyRange1 As Range If UCase(Range("E1").Value) < "HEADER" Then Exit Sub lastrow = Cells(Rows.Count, "E").End(xlUp).Row Set MyRange = Range("E2:E" & lastrow) For Each c In MyRange If IsEmpty(c) 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 " wrote: I have data in column E which consists of names. some rows have data (names) and some does not (blank). I want to delete all rows where column e has blank cells and where the data = "header". For instance, lets say the column E looks like this (Range E1:E23) header name1 name2 name3 header name4 name5 name6 header name7. The desired output i want is like this (Range E1:E7) name1 name2 name3 name4 name5 name6 name7 The last row is unknown, we therefore need to identify the last row first and then run the macro Can somebody help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing unnecessary rows through macro
Thanks for the speedy reply. It gives me result as
header name1 name2 name3 header name4 name5 name6 header name7 I want it as name1 name2 name3 name4 name5 name6 name7 I also want to delete rows where it says "header" I tried to modify the code by change the line If IsEmpty(c) to If IsEmpty(c) Or c.Value = "header" Then but it does not work. I don't know much programming. Can you help further? On Oct 14, 3:07*pm, Mike H wrote: Hi, This looks for the value "Header" in e1 and if it finds it deletes blank rows. Right click your sheet tab, view code and paste this and run it. Sub marine() Dim MyRange As Range, MyRange1 As Range If UCase(Range("E1").Value) < "HEADER" Then Exit Sub lastrow = Cells(Rows.Count, "E").End(xlUp).Row Set MyRange = Range("E2:E" & lastrow) For Each c In MyRange If IsEmpty(c) 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 " wrote: I have data in column E which consists of names. some rows have data (names) and some does not (blank). I want to delete all rows where column e has blank cells and where the data = "header". For instance, lets say the column E looks like this (Range E1:E23) header name1 name2 name3 header name4 name5 name6 header name7. The desired output i want is like this (Range E1:E7) name1 name2 name3 name4 name5 name6 name7 The last row is unknown, we therefore need to identify the last row first and then run the macro Can somebody help?- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing unnecessary rows through macro
I misunderstood,
Try this instead Sub marine() Dim MyColumn As String Dim MyRange As Range, MyRange1 As Range lastrow = Cells(Rows.Count, "E").End(xlUp).Row Set MyRange = Range("E1:E" & lastrow) For Each c In MyRange If IsEmpty(c) Or UCase(c.Value) = "HEADER" 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 " wrote: Thanks for the speedy reply. It gives me result as header name1 name2 name3 header name4 name5 name6 header name7 I want it as name1 name2 name3 name4 name5 name6 name7 I also want to delete rows where it says "header" I tried to modify the code by change the line If IsEmpty(c) to If IsEmpty(c) Or c.Value = "header" Then but it does not work. I don't know much programming. Can you help further? On Oct 14, 3:07 pm, Mike H wrote: Hi, This looks for the value "Header" in e1 and if it finds it deletes blank rows. Right click your sheet tab, view code and paste this and run it. Sub marine() Dim MyRange As Range, MyRange1 As Range If UCase(Range("E1").Value) < "HEADER" Then Exit Sub lastrow = Cells(Rows.Count, "E").End(xlUp).Row Set MyRange = Range("E2:E" & lastrow) For Each c In MyRange If IsEmpty(c) 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 " wrote: I have data in column E which consists of names. some rows have data (names) and some does not (blank). I want to delete all rows where column e has blank cells and where the data = "header". For instance, lets say the column E looks like this (Range E1:E23) header name1 name2 name3 header name4 name5 name6 header name7. The desired output i want is like this (Range E1:E7) name1 name2 name3 name4 name5 name6 name7 The last row is unknown, we therefore need to identify the last row first and then run the macro Can somebody help?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing unnecessary rows through macro
Super !!!
I want to clarify a doubt. I am going to put this code in a blank .xla file and create a template (Addin) and give a shortcut key. I am guessing that when I press the shortcut key, it will just run the macro irrespective of which excel file is opened. Is that right? If yes, is it possible to do a check before running the macro? For instance, find three unique words in the entire excel file "Manipulation" "Reversal" and "Movement Control". If these three words are present, only then run the macro. (This is to check if the correct file is open) Thanks again On Oct 14, 3:34*pm, Mike H wrote: I misunderstood, Try this instead Sub marine() |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing unnecessary rows through macro
Hi,
There' no reason this won't run as an addin but it worksheet code and works on the active sheet. To check if your in the correct workbook you will need to call another routine to check for those values and pass a variable back to the sub try this. The sub now call a sub which must go in a general module and pases the variable 'rightbook' Sub marine() MySearch If Not RightBook Then Exit Sub Dim MyRange As Range, MyRange1 As Range If UCase(Range("E1").Value) < "HEADER" Then Exit Sub lastrow = Cells(Rows.Count, "E").End(xlUp).Row Set MyRange = Range("E2:E" & lastrow) For Each c In MyRange If IsEmpty(c) 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 Public RightBook As Boolean Sub MySearch() RightBook = False For Each ws In Worksheets ws.Select With ActiveSheet.UsedRange Set c1 = .Find("Manipulation", LookIn:=xlValues) Set c2 = .Find("Reversal", LookIn:=xlValues) Set c3 = .Find("Movement Control", LookIn:=xlValues) End With Next On Error GoTo 100 If c1 < "" And c2 < "" And c3 < "" Then RightBook = True RightBook = True 100: End Sub Mike " wrote: Super !!! I want to clarify a doubt. I am going to put this code in a blank .xla file and create a template (Addin) and give a shortcut key. I am guessing that when I press the shortcut key, it will just run the macro irrespective of which excel file is opened. Is that right? If yes, is it possible to do a check before running the macro? For instance, find three unique words in the entire excel file "Manipulation" "Reversal" and "Movement Control". If these three words are present, only then run the macro. (This is to check if the correct file is open) Thanks again On Oct 14, 3:34 pm, Mike H wrote: I misunderstood, Try this instead Sub marine() |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing unnecessary rows through macro
I am confused :(
I pasted the entire code in the Insert-Module section and saved the file as a template .xla Then I opened my file and ran the shortcut key but it shows an error Compile Error: Only comments may appear after End Sub, End Function, End Property. What am I supposed to do? I want all the coding in the .xla file which I will use as an addin. After that whatever file I open, I should be able to call the program using the shortcut key which I have set. Please help On Oct 14, 5:51*pm, Mike H wrote: Hi, There' no reason this won't run as an addin but it worksheet code and works on the active sheet. To check if your in the correct workbook you will need to call another routine to check for those values and pass a variable back to the sub try this. The sub now call a sub which must go in a general module and pases the variable 'rightbook' |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Unnecessary Rows | Excel Discussion (Misc queries) | |||
Macro for removing columns/rows, freezing panes etc? | New Users to Excel | |||
removing unnecessary spaces from multiple cells | Excel Worksheet Functions | |||
Removing rows via macro/VBS script | Excel Programming | |||
Removing Unnecessary Macro Security Warnings | Excel Discussion (Misc queries) |