Easy Macro Question

seanbanks

New Member
Joined
Apr 8, 2009
Messages
12
Hi all,

I'm trying to figure out a macro and I'm sure its an easy fix but I can't figure it out. I need to delete all rows when a specific column has a blank cell. The problem is that column isn't always the same. But it always has the same name, "keep_it".

In summary, I need to find a column "keep_it" and delete all rows in which "keep_it" has blank values, but in some worksheets "keep_it" is in column C (for example) and in others it is in a different location.

Thank you in advance,
Sean
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
Hi Sean

Your question is a little fuzzy to understand let me know if my understanding below is accurate.

You have several sheets in a workbook in which you need to cycle through each worksheet and find the column where "keep it" exists. Once you determine the column you would like to cycle through all records in this column and particular worksheet and delete all records where no values exists in this column...
 

seanbanks

New Member
Joined
Apr 8, 2009
Messages
12
That is correct. The different worksheets are in several workbooks. Other than that, you've got it correct.
 

Diablo II

Well-known Member
Joined
Sep 28, 2008
Messages
538
this will work on any column you pick and will delet any empty row and move it up.
Code:
Sub test()
 Dim theRange As Range
 Dim nCells As Integer
 Dim I As Integer
   Set theRange = Selection
   nCells = theRange.cells.Count
   For I = nCells To 1 Step -1
     If theRange.cells(I).Value = 0 Then
        theRange.cells(I).EntireRow.Delete
     End If
   Next
End Sub
 

jfklbj

Board Regular
Joined
Feb 9, 2009
Messages
68

ADVERTISEMENT

I'm not sure how your data is arranged, but would it be more effective to copy all the data that you want to keep to a new worksheet or workbook?
 

seanbanks

New Member
Joined
Apr 8, 2009
Messages
12
Thanks, Diablo. But I'm not sure how this helps. It seems to just delete one row at a time? Maybe I'm doing something wrong.
 
Last edited:

seanbanks

New Member
Joined
Apr 8, 2009
Messages
12

ADVERTISEMENT

hi jfklbj... no i'd like to keep the data in the same worksheet/
 

Diablo II

Well-known Member
Joined
Sep 28, 2008
Messages
538
go to the last entery and click and go to the top pick from bottom to the top then run.
 

seanbanks

New Member
Joined
Apr 8, 2009
Messages
12
So I have to highlight all of the data. I see. Ok, is there code that I can add to that which will highlight all of the data for you? Some of these worksheets have 10,000+ rows.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top