Removing unnecessary rows through macro

[gojakie]

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?

 

[Mike H]

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

 

[gojakie]

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?

 

[Mike H]

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

 

[gojakie]

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

 

[Mike H]

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

 

[gojakie]

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