Over 1 million tech questions and answers.

Deleting rows based on empty cell.

Q: Deleting rows based on empty cell.

You must be bored with this question cos I've seen lots of them, however I can't seem to fathom out what to do..........

I've got a block of data within a spreadsheet 4 rows always in the same place.

How do I put into code:-

If column d43:e46 is blank delete rows 43 - 46 if not ignore it.

As I said I've tried a few things and it will delete the four rows regardless of them being empty of not.

Can someone help please.

Many thanks,

Moll

RELEVANCY SCORE 200
Preferred Solution: Deleting rows based on empty cell.

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Deleting rows based on empty cell.

Is there anybody there? - Help!

Read other 2 answers
RELEVANCY SCORE 100.8

Hi I am trying to copy and insert rows based on a number in cell E. If the number in cell E is 4, I would like to copy 3 additional rows beneath the original and then also number the 4 rows into column F consecutively for each group. I am attaching a spreadsheet of before and after. Sheet 1 is the before and sheet 2 is the after. Can anyone help me with this. I have tried another code but it not work properly. I did find one that would put blank rows in but I cannot get past that. Any input is greatly appreciated.

Thank you

Peg
 

A:insert rows based on number in cell and copy the data down into the new rows

Hi Peg

Try this solution and see if it is ok for you. I've put in some code to help prevent the running of the code multiple times on the same sheet. If you don't need this safeguard you can delete the block of code that does this.

Also, make sure and make a backup of your file before running this - just in case.
 

Read other 1 answers
RELEVANCY SCORE 90.4

I have a sheet with several hundred rows. Starting in Row 4 through the end of the sheet are mixed dates in column 1 and ALL rows from Row 4 through the end of the sheet hidden. I want to be able to type in a date in Cell A1 and have all rows that have that date in Column 1, Row 4 through the end of the sheet to become unhidden. Secondly, I would like to type some other code like "999" or something in Cell A1 to have ALL rows become unhidden. Thirdly, I would like to either type another code or just delete anything in Cell A1 and have ALL rows from Row 4 through the end of the sheet to become hidden again.
Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM)2 Duo CPU T6600 @ 2.20GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 3963 Mb
Graphics Card: Mobile Intel(R) 4 Series Express Chipset Family, 1853 Mb
Hard Drives: C: 453 GB (298 GB Free); F: 931 GB (710 GB Free);
Motherboard: TOSHIBA, Portable PC
Antivirus: Webroot SecureAnywhere, Enabled and Updated
 

Read other answers
RELEVANCY SCORE 90.4

Hi,
I am trying to hide rows in excel 2010 based on cell values in a certain column. I know that I have to use the VBA but I don't know how to do that kind of stuff. I know it should be simple to do but I don't know where to start. Let me know what you need in order to help me accomplish this task.
 

A:How to hide rows in excell based on cell value

Read other 8 answers
RELEVANCY SCORE 90.4

Dear Fellows

I am looking for help for a macro which can copy data to other sheet based on adjacent cell value which i define.

For example i have data attached in a sheet.

i want to copy data based on value present in E column to different sheets. if it is ES then all ES rows should be copied to sheet ES with roll number name father per and dept.

Same is for other departments, on different sheets. There will be 9 departments overall.

Looking for response
 

Read other answers
RELEVANCY SCORE 89.2

Hi all, long time reader, first time poster. I searched around and have found similar queries to what I require however I haven't been able to adapt these solutions with any success so here we go....

I have a list of businesses in a spreadsheet with information such as name, phone number, e-mail, website, postcode etc. I would like a search box on the side, say in cell O2 where a user can input part or all of a postcode e.g. (LS19 or LS) and those that do not begin with LS would be hidden. The postcodes are in column G.

I'm trying to make this as simple and user-friendly as possible so employees can find businesses in a certain area with ease. Thanks in advance for any help and please feel free to ask questions.

Chris Needham
 

A:Excel VBA : Hide rows based on cell input.

Seems nobody had the answer so I found a work around myself. Just in case anybody else wants to do a similar thing. Using auto filter is the easiest way.
Code:

[SIZE="3"]Sub Search()
Columns("G:G").Select
Selection.AutoFilter
ActiveSheet.Range("$G$1:$G$999").AutoFilter Field:=1, Criteria1:="*" & Range("O2").text & "*", _
Operator:=xlAnd
End Sub[/SIZE]

Where G is the column to search for and O2 is the users input data.

Guess this can be closed now, thankyou all for your help.
 

Read other 1 answers
RELEVANCY SCORE 89.2

Hello to evrebody,
I have look into the answers and the solutions about "hide/unhide rows based on the cell data" but i am not abble to understand how its work and apply to my sheet.
On theattached sheet i explain what im looking for.
Pleaseo play it and hope to help me and give a solution please.
Thanks in advance.
Jose
 

Read other answers
RELEVANCY SCORE 84

I have a fairly simple task. From Row 17 to row 51 and Row 96 to 163 , I want to delete the row if cells P and Q are empty.

From row 57 to row 94 I want to delete the row if cells B, C, P, and Q are empty

I'm not sure what I'm doing wrong here.

Sub CleanUp()

Dim endrow As Long
Dim x As Integer

Set endrow = Sheets("Work Order").Range("A17").End(xlUp).Row

For x = endrow To 17 Step -1
If Sheets("Work Order").Range("P" & x) = "" And Sheets("Work Order").Range("Q" & x) = "" Then Sheets("Work Order").Rows(x).EntireRow.delete
End If
Next x

Rows("167:180").Select
Selection.delete shift:=x1Up
End Sub
Click to expand...

If you see something glaringly wrong or know a quicker way to do this, I would be very thankful.
 

A:VBA Deleting row based on cell value

Here's an example work sheet. If the code works right in the example, it would delete rows 4, 5, 9, and 10.

Code:
Dim endrow As Long
Dim x As Long

For x = endrow To 29 Step -1
If Sheets("Sheet1").Range("P" & x).Value = "" And Sheets("Sheet1").Range("Q" & x).Value = "" Then
Sheets("Sheet1").Rows(x).EntireRow.Delete
End If

Next x
Why won't it remove the rows?
 

Read other 2 answers
RELEVANCY SCORE 80

Hi.

I'm a beginner when it comes to VB Script and so I am struggling to work this out. I basically have a worksheet that contains information about different staff members. The sheet is set up so that column A (from row 8 on) is their name and columns B to L (also from row 8 on) are other staff attributes. Due to several reasons, there is more than one listing for each staff member (up to 40). The database is supposed to be there so that any staff member can come in and look up their information (or that of any other staff member). However, with so many listings and so many staff members, it becomes an unmanageable database. I could just create filters so that staff members could simply filter out other employees; however, this would create issues. Instead, I have created a cell above the table (say C2) where staff members can enter their name via means of a data validation list (which is linked to a list of staff members on another sheet). I would then like all rows in the table to hide except for those where the name in column A = C2.

I have searched this on the net and I keep seeing similar responses that all say it's hard when there is data validation / formulas involved. Is there a way this can be done?

Cheers
 

Read other answers
RELEVANCY SCORE 68.4

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 68.4

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 68.4

Hello,

I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you
 

A:Excel - Copy paste cell into range based on another cell

Read other 12 answers
RELEVANCY SCORE 67.6

Hello,

I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.
Code:
Sub REMOVE()


Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row


For i = 1 To p
Range("k2").Select
If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""
'If no Issue, Location/Obsevations should be blank
Next i


End Sub


 

A:Solved: Clearing Cell Contents Based off other Cell

hi
try this variation;
Sub REMOVE()

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & i), "None") > 0 Then
Range("L" & i) = ""
Range("M" & i) = ""
End If
'If no Issue, Location/Obsevations should be blank
Next i

End Sub
 

Read other 2 answers
RELEVANCY SCORE 61.6

Good morning,

I have a spreadsheet that itemises 1000's of transactions from different customers. It grows by the day - I have a macro that deletes the records I don't want to see based on the Customer name (delete row if <> to "ABC CO." etc.). What I would like to know is is there a way of doing this without looping through every line as it's taking longer to run every day.

Hope my explanation is sufficient.

Many thanks in advance.

Moll
 

A:Delete rows based on criteria - without looping

Read other 16 answers
RELEVANCY SCORE 61.6

Hi,
I have a list of 150 stores of which 20 are designated "gold" which means they get better service for deliveries. In the spreadsheet that contains these stores they are already configured to have the name of the store blocked in yellow (gold). I have to manually retype these onto another sheet, is there a way of transferring this info using the colour as the argument.

Many thanks.
 

A:copy rows into another sheet based on colour

Read other 10 answers
RELEVANCY SCORE 61.6

How can I hide rows based on the selection of a Data Validation in Excel.

Example:

If user chooses 1 from drop-down menu then hide rows 15-20 otherwise if 2 is chosen hide rows 25-35. I have a proc that will hide the rows but I don't know how to get the code to fire.

Thanks
 

A:Hiding rows based on range content

Read other 7 answers
RELEVANCY SCORE 61.6

Hi I'm absolutely struggling with this:

I have an Excel file with 2 worksheets.

In Sheet1 is a drop-down list of countries (A1), and in Sheet2 is the Data.
The Data (Sheet2) has a country field in Column A.

What I want is to ONLY display data rows in Sheet2 (or possibly a new sheet), where the country matches the country selected from the drop down entry on Sheet1.

Any Ideas?

 

A:Return Rows in one sheet based on criteria from another

Read other 7 answers
RELEVANCY SCORE 61.6

Hi All,

So I'm fairly competent in Excel in most things other than macros (which means it could be argued I know nothing about Excel...)

I'm looking for a way to automatically hide rows from a page. Essentially it's a progression tracking sheet that I've got, and I want things to stay on the sheet for only 5 days after the job is complete. I have a TODAY() function which I am using to determine whether or not it has been 5 days since completion.

Any advice would be more than appreciated and I thank you all in advance.

Tom
 

A:Excel: Automatically hide rows based on value

Bumping due to inactivity. Not sure if it's allowed but hey, I still haven't got an answer...
 

Read other 2 answers
RELEVANCY SCORE 61.6

Hi,

I need help. I am trying to merge a document which provides department name (a merged cell in excel) and the details of its employees - name, address, phone, title etc. in to a word document.
Is there a way I can merge these multiple data points corresponding to just one cell? Given below is an example

thanks.
Department NameEmployee Name TitleAddressPhonePurchaseaaaaaa11axyza1234bbbbbb22bxyzb1234cccccc33cxyzc1234dddddd44dxyzd1234Salesxxxxxx11xxyzx1234yyyyyy22yxyzy1234zzzzzz33zxyzz1234
 

Read other answers
RELEVANCY SCORE 61.6

Can you help me understand how to Concatenate all rows of a bunch of records in one column, but with varying numbers of rows and delineated by a keyword indicating the first row like 'File*#' ? The file here gives an example. Thank you!
 

A:Add equal number of rows between cell with keyword

Hi, If I understand you perfectly, I see that you are receiving a *.txt file with information, If this is correct I prefer that you upload an attachment with this file (the *.txt file)... for me in this case is easier to read this file and then put the result in Worksheet.

Hernan Torres
Maracaibo, Venezuela
 

Read other 1 answers
RELEVANCY SCORE 61.6

I have to run these reports at work that contain UPC numbers. And sometimes, with how things work, I have UPC numbers, and their characteristics, that I have already reviewed. For a short example, UPC# 123456789 has certain characteristics associated with it. It is on my last week's report, so i have already reviewed it. UPC#0000000000000 and 11-111111-11111 and 123456789 are all on this week's report. I need to review UPC#0000000000000 and 1111111111111, but since i have already reviewed 123456789, I want to delete it, and all of the rows that contain its characteristics.

Needless to say, when I run into this issue, I end up needing to delete about 50 or 60 UPC #'s and their characterisitics. As you can see in the attached worksheet, there is alot of data under each UPC#.

So what I want to be able to do is search the entire sheet, for what is in column J, which is where i list my UPC's to delete. Once i find that UPC number(oh and the UPC is not going to be in a cell by itself, but the cell contains the UPC # somewhere in it) I want to delete all of the characterisitics that are associated with that UPC. I have hightlighted in my example what i would like to delete, after each find is completed, which includes the last row, which is empty. Also, column J will contain more than one UPC, i just used only 1 to keep the example simple.

Any help on this would be greatly appreciated!
 

Read other answers
RELEVANCY SCORE 61.6

I have a spreadsheet that has name in cell A1 and ID numbers in another cell O1. O1 has multiple listings seperated by a comma such as 456, 234, 432, etc.

Is there a way to separate out the text in O1 and still include all the other information from the other cells.

EX.

A1 Name Joe
B1 Address 123 Main
C1 State WY
D1 Alias .... Other info
O1 ID 456

A2 Name Joe
B2 Address 123 Main
C2 State WY
D2 Alias .... Other info
O2 ID 123

Does this make sense?
 

A:Seperating Text in Cell to multiple rows

Read other 13 answers
RELEVANCY SCORE 61.6

Hey,

I've run into a problem in excel that I'm not to sure how to solve. I've looked through numerous other posts about condensing data from multiple rows into columns but haven't run across a method of condensing multiple rows into a single cell on a new line.

Here is what I am trying to do.

I have a sheet with two columns which looks something like this:

ID Software
1 Software A
1 Software B
2 Software B
2 Software C
and so on... Each ID can have up to 50 Software items, what I need for a project database is for each piece of software to be in the same cell on a new line, like this:
ID Software
1 Software A
Software B
2 Software B
Software C

Where ID is one cell and the corresponding software is in another cell. I can then perform a vlookup to import this into the project database based on ID.

I was able to use code from another post to move those rows into multiple columns so that each ID has only one record, now is there an easy way to move those items all into one column?
Code:

Sub MoveRows()
vStart = 2
Do Until Range("A" & vStart).Row = Cells(Rows.Count, "A").End(xlUp).Row + 1
If Range("A" & vStart).Value = Range("A" & vStart + 1).Value Then
Range("B1:G1").Copy Destination:=Cells(1, Cells(vStart, Columns.Count).End(xlToLeft).Column + 1)
Range("B" & vStart + 1 & ":G" & vStart + 1).Copy Destination:=Cells(vStart, Cells(vStart, Columns.Count).End(... Read more

A:Condensing Multiple Rows into a Single Cell

Code:

Sub ConcateRows()
Dim start As Range
Set start = Range("A2")
Do Until start = vbNullString
If start = start.Offset(1) Then
start.Offset(, 1) = start.Offset(, 1) & ", " & start.Offset(1, 1)
start.Offset(1).EntireRow.Delete
End If
Set start = start.Offset(1)
Loop
End Sub
 

Read other 1 answers
RELEVANCY SCORE 61.6

Hi All- a newbie,

I've been trying to figure this out since yesterday through Excel forums, but to no avail.

This is my basic table I currently have.

Col1 Col2 Col3 Col4 Col5 Col6 Col7
Item1 24 2.00 48 1.50 96 1.00
Item2 24 3.00 48 2.50 96 2.00

I want to be able to use a macro and do the following for everyrow in the Worksheet. Basically copy two different ranges and paste in new rows.

Col1 Col2 Col3
Item1 24 2.00
Item1 48 1.50
Item1 96 1.00
Item2 24 3.00
Item2 48 2.50
Item3 96 2.00

Any help is appreciated.
 

A:How do I Copy Two Cell Ranges to New Blank 2 Rows?

Welcome to the board.

We don't know if there are any blank cells in your current setup. If there aren't*, try this (which assumes cols I:K are free):

Sub test()
Range("I1") = "Item"
Range("J1") = "1st"
Range("K1") = "2nd"
x = Range("A" & Rows.Count).End(xlUp).Row

For Each Cell In Range("A1:A" & x)
Range("I" & Rows.Count).End(xlUp).Offset(1).Resize(3) = Cell
Cells(Cell.Row, 2).Resize(, 2).Copy _
Range("J" & Rows.Count).End(xlUp).Offset(1)
Cells(Cell.Row, 4).Resize(, 2).Copy _
Range("J" & Rows.Count).End(xlUp).Offset(1)
Cells(Cell.Row, 6).Resize(, 2).Copy _
Range("J" & Rows.Count).End(xlUp).Offset(1)
Next Cell
End Sub

*if there are, post back for some fancy formulas.
 

Read other 3 answers
RELEVANCY SCORE 61.2

Hi everybody,

I work in accounting (yeah I know loads of fun )... I have a spreadsheet that I have created to log the cash drops of several cashiers (about 140 currently) that compares it to the readings on our different point of sales systems.

this file has 31 tabs (one for each day of the month) and each cashier has the same row for his/hers information thruout the month. Each day we log how much people are dropping in cash/travelers checks, etc and compare it to what the system is telling us that they should have dropped.

Our policy is that any variance in above $10 (over or short) needs to be investigated by the manager/supervisor of the area in question and the general cashier and income audit manager should also be notified.

What I would like for excel do (not sure if it is possible) is after the general cashier logs the drops and excel tells me if each cashier has a variance above the threashold or not; to send an e-mail with the row containing the cashier's information for that day to supervisor/manager and the accounting folks involved automatically.

I am pretty novice when it comes to VBA and as much as I have been looking online and specially in this forum I haven't been able to come up with a solution to this.

Thank you in advance to anyone that can point me in the right direction.
 

A:EXCEL - Email rows to various addresses based on certain criteria

Well I guess nobody has any ideas
 

Read other 2 answers
RELEVANCY SCORE 61.2

Hi,

Is it possible to hide the entire row if the formula result is "x". (in Excel)

In my spreadsheet, I have vast numbers of results that show up as N/A. This is the correct answer, but it's making the spreadsheet hard to read because of how many there are. It's easy enough to sort afterwards, but I was hoping a a more elegant solution existed?

Thanks for any help!
 

A:Solved: Hiding rows based on formula result

Read other 13 answers
RELEVANCY SCORE 61.2

Hi all,

I would like to conditionally format the attached spreadsheet so that each alternating week is shaded on whole set of rows.

Also with our roster the week runs Thurs to Wed.

I've looked quite a bit into conditional formatting now. But this one has me stumped.

Thanks for any advice.
 

A:Excel: Format rows based on date and a few other specifics

Read other 7 answers
RELEVANCY SCORE 61.2

I have attached two images of an Excel worksheet. I have gone to the trouble of taking multiple sets of three columns of associated data, that is data of x, y, z where y and z are associated with x. In building a new table with six sets of data, I put x1, x2, ..., x6 in a single column, and then spaced out y1/z1, y2/z2, ..., y6/z6 in separate columns before a sort. I then sorted on values x (all x1 to x6 in a single column).

The result is shown in the first image "pre-combine-rows"

You can see that in column "all x," many rows have identical values: for example there are five rows with value x = 218.9, and six rows with value x = 224.9. I want to combine all those rows, which combines also the values in the columns y1/z1 through y6/z6.

The result should look like the second image: "post-combine-rows"

20 rows has been combined into 5 rows.

This is a walk in the park for Excel, right? Does not even need a macro, right?

Is this one of these Database/Criteria setups?

A:Excel: Combining Rows Based on An Equal Value in One Column

Hi mavigozler,

You wouldn't require a macro to complete this feat. You can achieve the wanted result with formulas. If you still need help with this issue, I'll be glad to elaborate a formula for you.

Regards,
Peatawn

Edit: You can also have a look at the Excel matrix feature...

Read other 4 answers
RELEVANCY SCORE 61.2

How do I hide and show different columns based on values selected from a drop-down list? I wrote a code to do this but there has to be an easier way.

Cell C2 has a drop-down list. This list is from another part of the workbook. Next to the list in the workbook, I put values representing the beginning of the columns I want to Show.

Units 4
Intervals 5
Time 6
All 0

On the page that I am working with I have created a spreadsheet that tracks monthly amounts of Units, Intervals and Time (in separate columns). I want to be able to select "Units" from C2 and show columns, 4, 7, 10, 13... +3 each) then be able to show Intervals (and only show columns 5, 8, 11, 14... etc), then Time (and show columns 6, 9, 12, 15... etc) and if I select All, I want to show everything.

Dim x As Integer
Dim vCriteria
vCriteria = Range("C4")

If vCriteria = "0" Then
Columns("D:AS").Select
Selection.EntireColumn.Hidden = False

Else
Columns(vCriteria).Select
Selection.EntireColumn.Hidden = False

Columns(vCriteria + 3).Select
Selection.EntireColumn.Hidden = False
Columns(vCriteria + 6).Select
Selection.EntireColumn.Hidden = False
Columns(vCriteria + 9).Select
Selection.EntireColumn.Hidden = False
'... and repeat til vCriteria + 40 (column AS)

End If
End Sub

This works. But....
There has to be an easier way to write this code. Additionally, I want to be able to add more more columns to this spreadsheet as it is tracking monthly numbers so I don't wa... Read more

A:Excel VBA: Show/Hide rows based on dropdown value

Read other 8 answers
RELEVANCY SCORE 60.8

Hi there,
I got one excel file with 1000 rows in it. Now it got 7 different columns. There is one column which is "email. Now, this email column contains value "admin###email.com" , "saud", sund. Now what i want , all the rows which contain admin###email.com should be separated from all other emails. It can be separate excel file or may be top few hundred rows.

how to do this?

Thanks
 

A:excel problem (sort rows by judging particular value of cell)

Have you tried Autofilter? It is in the Data/Filter menu. Or, the other possibility is Data/Sort.

Jimmy
 

Read other 2 answers
RELEVANCY SCORE 60.4

The following code deletes all empty rows after any row that has words in a cell....but when I run the macro, it still askes from Excel how many rows do go down....so it starts at A1 but I have to add when asked how many rows to move down over....

Is there something to add, where I can automatically put in the macro how many rows to move down on, as this is a macro that runs automatically as part of excel form I use...thanks so much and happy to hear back very soon:
Code:
Sub DeleteEmptyRows()
'
'This macro will delete all rows, which are missing data in a
'particular column, underneath and including the selected cell.
'
Dim Counter
Dim i As Integer
Counter = InputBox("Enter the total number of rows to process")
'ActiveCell.Select
Range("A1").Select
For i = 1 To Counter
If ActiveCell = "" Then
Selection.EntireRow.Delete
Counter = Counter - 1
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub

 

A:Solved: Excel Detete Empty Rows

Hi
Try this on a copy of your file....

Code:
Sub DeleteEmptyRows_2()
Dim i As Integer
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1) = "" Then
Cells(i, 1).EntireRow.Delete
End If
Next i

End Sub

 

Read other 2 answers
RELEVANCY SCORE 60.4

I have a sales report that shows number of leads by sales person and date. It's organized into a monthly calendar view (with dates running down instead of across) and I've used formulas so that I only need to change the year to autopopulate the dates to fall within the corresponding month. What I want to do is only show the actual business days in each month - so auto hide any Sunday or Saturday rows as well as the blank rows (just the rows that have formulas in column A, not the blank spacer rows) so that when I update for each year I don't have to manually hide the rows with no data. Can I do this with VBA code? I'm not sure if I've explained myself well, so please let me know if this doesn't make sense! I've attached my spreadsheet.

For Windows XP, Excel 2010

Thanks!
 

A:Solved: Auto hide rows based on formula results

Read other 7 answers
RELEVANCY SCORE 60.4

Hi there guys

Looking for help in excel 2007 for the macro part.

Basically in sheet1(BOM), we have configurations which our customer order to us (in the form of CP1,CP2..CPn). In Sheet2 (CP) details about CP are mentioned, basically each CP has list of items under it. Typical order from customer includes combination i.e. CP1&CP2 or could be CP2&CP3. What I want to do is based on the order in BOM sheet, need to search the details of CP's in sheet2 (CP) & copy the rows belong to that particular CP in the BOM sheet. So that I can make the BOM list for that particular order. Each CP has variable number of rows. Appreciate your help in making macro for this.

Rgds, MintC
 

A:Excel Macro to copy multiple rows based on selection

Read other 11 answers
RELEVANCY SCORE 60.4

Hi,

I have been working on a Macro in excel to take a specific criteria (cell B2, Sheet1) and search column A sheet3 for this criteria. If it is found paste that row (A-H) on sheet1 beginning at A:17. There is the possibility for 2+ rows of data with this specific criteria. I want to be able to type in cell B2 on sheet1 and have the macro lookup all rows on sheet3 and paste them beginning at A:17 on sheet1. Any help on this would be much appreciated.
 

A:Solved: Copy multiple rows in excel based on one criteria

Read other 9 answers
RELEVANCY SCORE 60.4

Hi everyone,
I am new to this Forum.
I have a workbook with multiple sheets Sheet1, Sheet2, Sheet3, etc.) each with similar information with same column headings. I want to copy multiple rows from each of these sheets into a common Summary sheet based on:

1. if the column value (Column J) is greater than zero

2. if the column (Column B) contains a certain word such as "Total", "New", or "Summary"

I need to create and use two seperate macros one for each of the above conditions.

I hope I was clear enough in explaining my situation. Attached also please find the sample data. Appreciate your help and direction! Thanks a lot!!!

Regards,
KBM
 

Read other answers
RELEVANCY SCORE 60.4

Hi all,

How do you ensure excel autohides blank rows that are in various formats and still shows values that do have values? (eg. $.00)

This thread has helped:

http://forums.techguy.org/business-applications/385564-solved-autohide-rows-excel.html

However, for some reason - if the cells are in $.00 format and have values in them - they are also hidden.

Thank you,
 

Read other answers
RELEVANCY SCORE 60.4

Hello to all,

I'm new to VBA in general and I'm in need of help in making my project works. I'm currently working on hidden in-sheet menu (rows 2 to 6) that could by unhidden by double-clicking on the cell "D1" and hidden again by double-clicking on "A6". I want to use that menu in every sheets.

I would really appreciate any inputs on this matter. Feel free to view my spreadsheet for a better idea.

Thanks in advance!
 

Read other answers
RELEVANCY SCORE 60.4

Good Day,

I would like to copy the rows in a worksheet to another worksheet where a value in the row say H2 <900. I would then like to delete those cells from the former worksheet. Can you assist with this. Thanks
 

A:Solved: Macro to delete rows where cell meet a condition

Read other 7 answers
RELEVANCY SCORE 60

In sheet 1, I have a list of data in A221. In sheet 2, I have formulas in the same range that ‘paste link’ the data. If, there is no data entered in any one entire row of the same range in sheet 1, then I want the entire row to hide automatically in sheet 2. In fact, unless there is something entered to start with, I do not want any of the rows to be visible. Is this possible and how? Thanks.
 

A:auto hide empty rows excel 2003

Its still me, jmannsr, trying to figure this forum out. I have an excel work book that I would love to attach or send to someone that would explain what I need help on. Any ideas how to attach an excel book here or any one willing...I can email it directly to you? Thanks!
 

Read other 3 answers
RELEVANCY SCORE 60

Sample file attached.

I am trying to combine info from three columns into one cell. In some instances, Column C will be empty, but if it is not empty, I want to have a "&" in between the two names.

I have come as far as:

=CONCATENATE(B5," ",A5," ","&"," ",C5,) BUT - that still leaves the "&" in the single names.

Thank you
 

A:Solved: Concatenate Excel rows with some empty cells

=if(c2="",b2&" "&a2,b2&" "&a2&" & "&c2)
 

Read other 3 answers
RELEVANCY SCORE 59.6

Good day everyone.
Basically I need to have a formula in excel that includes auto hide and unhide of rows based on the result value. Attached is my sample exercise for quick reference. In this exercise, I want to hide automatically the rows under "REPORT OUTPUT" that contains "0" ZERO value. Basic guide: Once you enter value (from 1 to 5) in cell D3, report output will automatically calculate...... The missing condition in the formula is to automatically hide ZERO value.... Please help.
Thanks in advance.
 

A:Solved: Formula with auto hide and unhide rows based on result value

Read other 16 answers
RELEVANCY SCORE 59.6

I have the following data that i need to create a set of rules or code that if multiple criteria is met to delete the row. I have included some examples of what i need. i tried doing a Recording macro but didnt work since sometimes some of the criteria is not there at the time of running the macro and it fails.

I need to have code look for specific values, i.e.

Account paid Y
Date of Payment Blank
Highlight all cells
'this is to review information
after this then check if it has Code 3 = C1 and Code 4=D1
Highlight all cells
'this is to review information

After
Check if Code 1=B2
Check if Paycode is blank or other
Highlight all cells
'this is to review information
Then delete other rows



AccountBalanceDateCode 1Code 2Due dateCode 3Code 4Code 5Account paidPaycodeJonnathan5009/1/2011A1B1C1D1E1YCollectedJessica252.359/2/2011A2B2C2D2E2YCollectedMaria321.549/3/2011A3B3C3D3E3NPayPlanLeon652.339/4/2011A4B4C4D4E4NPayPlanDavis525.329/5/2011A5B5C5D5E5YCollectedCarmen277.679/6/2011A6B6C6D6E6NOtherKarina346.869/7/2011A7B7C7D7E7NJose 677.659/8/2011A1B1C1D1E1YCollectedMary550.649/9/2011A2B2C2D2E2YPayPlanMiguel302.999/10/2011A3B3C3D3E3NPayPlanJessica372.189/11/2011A4B4C4D4E4NCollectedElaine702.979/12/2011A5B5C5D5E5YOtherPatricia575.969/13/2011A6B6C6D6E6NCollectedKaren328.319/14/2011A7B7C7D7E7NCollecteddavid397.59/15/2011A1B1C1D1E1YPayPlanJoe728.299/16/2011A2B2C2D2E2YPayPlanJoseph601.289/17/2011A3B3C3D3E3NCollectedwilliam353.639/18/2011A4B4C4D4E4Nbill422.829/19/2011... Read more

A:MS Excel 2007 - VBA or Macro to delete rows based on multiple criteria.

Read other 8 answers
RELEVANCY SCORE 59.6

I have this spreadsheet where I am trying to get a macro to compare column b with a and if they match, to move column b under column a to reorganize a gigantic spreadsheet. Any ideas?

In the example below, the macro would match "eggs" in column b and move those rows under column a which has eggs too... i need the macro to do this for potential "juices" and "sodas" etc. As well as any other things that may come up. Thnx in advance.

A. B.
eggs bread
milk juice
soda eggs
Juice eggs
 

Read other answers
RELEVANCY SCORE 59.6

Hi there! I'm new to using these forums, but have spent some time scouring them from time to time to look for simple solutions to any excel/VBA problems I'm having. I consider myself relatively intermediate to advanced in Excel as far as formulas and spreadsheet creation goes, however VBA is foreign to me, and an area I'd love to learn. If any of you have recommendations on a good resource to begin learning VBA, please send it my way. And now....onto my macro question.

I've got a workbook with many tabs, and one or two input tabs feed information into another summary tab using various lookup and index formulas. There are any where from 40 to 80 rows to be qualified depending on the specific summary sheet (therefore I will likely need the macro to reference only the active sheet as it needs to work the same way on different sheets independent of each other). What I would like to do is if the result of the formula in column A of the summary sheet is "----------" (starts at A7) then I would like to hide that row, and if the data is changed on the input tab to result in anything other than the qualifying "----------", for that row to be unhidden. If this can be done dynamically that'd be great, however, if a button needs to be put into the spreadsheet to execute the macro, that will be fine too.

Thanks so much in advance for your help, and I promise I'll do my best to begin learning VBA so I can help contribute in return!
 

A:Macro to Hide/Unhide Rows Based on Results in Column Equation

HI,

Here is a code that may help you. You can copy and paste it too a module.
When you copy it to a module you can assign a "Short" cut key to activate the code.
To do this you select "Tool", "Macro" and when you see the name of the macro you want to run you can select it and then select "Options" and assign the short cut key. You will be able to use the short cut key on any active sheet.
Code:

Sub Hide_Row()
Application.ScreenUpdating = False
Dim Mysh As Worksheet
Dim cel As Range
Set Mysh = ActiveSheet
For Each cel In Mysh.Range("A1:A" & Mysh.Range("A65536").End(xlUp).Row)
If Not (Mysh.Cells(cel.Row, 1).Value Like "--*") Then
Mysh.Cells(cel.Row, 1).EntireRow.Hidden = False
Else
Mysh.Cells(cel.Row, 1).EntireRow.Hidden = True
End If
Next
End Sub
 

Read other 2 answers
RELEVANCY SCORE 59.2

I have Win XP, Excel 2003, and I want to delete all, not just duplicate, rows in a column of numbers. Example:
101
102
103
104
105
102
This one last entry (102) is duplicate. I want this 6th row deleted AS WELL AS the 2nd row so the only ones left are the numbers that are unique. Is there a formula I can throw in?
 

A:Deleting all duplicate rows

Read other 12 answers
RELEVANCY SCORE 59.2

Excel Masters, I am not sure how to loop through the following code for multiple row formatting. The code works fine if the user has selected cells within a single row. However, when multiple rows are selected, the 'With ActiveCell' formatting only works for the first row selected. How can I loop the 'else' statement to format the ranges for all rows with a cell selected?
Private Sub Button_DeleteRow_Click()
Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then

Selection.EntireRow.Delete

Else

Selection.EntireRow.Interior.ColorIndex = xlNone
With ActiveCell
Range(Cells(.Row, "AA"), Cells(.Row, "AN")).Interior.ColorIndex = 15
Range(Cells(.Row, "c"), Cells(.Row, "d")).Interior.ColorIndex = 15
Range(Cells(.Row, "a"), Cells(.Row, "a")).Interior.ColorIndex = 15
Range(Cells(.Row, "j"), Cells(.Row, "j")).Interior.ColorIndex = 15
Range(Cells(.Row, "n"), Cells(.Row, "n")).Interior.ColorIndex = 15
End With

End If

End Sub

On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.
 

A:Solved: Excel Macro - Loop selected-cell formatting for multiple rows

Something like:

Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then

Selection.EntireRow.Delete

Else

Selection.EntireRow.Interior.ColorIndex = xlNone
SelRows = Selection.Resize(, 1).Cells.Count
Cells(ActiveCell.Row, 27).Resize(SelRows, 14).Interior.ColorIndex = 15

'(and so on)

End If

?

On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.Click to expand...

If you mean "how do I bypass that for Selection.EntireRow.Delete then:

Application.EnableEvents = False
Selection.EntireRow.Delete
Application.EnableEvents = True
 

Read other 2 answers