I have a worksheet called "Online Post" and I need a way to do the following.
Check for two blank rows next to each other that have :a background and border, if true then do a Edit -> Clear -> All
So when the file is exported to HTML it doesn't include the blank rows.
I thought about using a marco that just deletes all the blank rows but I have blank row splitting up sections of the page.
I can count the blank cells withiin a range using
=COUNTBLANK(C6:AD2506)
But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.
It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.
I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:
Supplier
Invoice Date
GL Date
Invoice Amt
[Code].....
I am working in the attached spreadsheet. For anyone who does not wish to open the link, let's say these sheets are titled 'Sheet1' and 'Sheet2'.
Sheet2 looks like this:
Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
[Code]....
Now, how may I pull this data into a summary sheet (Sheet1) that skips over any instances where column A ("Year") is blank so it looks like this:
Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
2013 2 Pat
2013 5 Bob
[Code]...
Note: All columns are the EXACT same across each sheet(including Sheet1, Sheet2, Sheet3), if that extra piece of information works.
PS It is fine if, in order for a formula to work, I need to copy and paste the formula for the length of rows per each Sheet to consolidate.
EDIT:
So, in my industry, any summary-level data (shown on Sheet1) must have a linked reference to a particular sheet (Sheet2, Sheet3, etc.) so the user knows where the raw data came from. This is to ensure increased accuracy of data and minimize human error (like, if for example, I accidentally didn't copy and paste all the rows correctly).
I have uploaded the actual sheet to show what the data actually look like. I am trying to pull in 2013 data into my "Summary Page" and would like to have all the data linked to the '2013' sheet but skip any blank rows.
Here is my failed formula:
=INDEX('2013'!F$3:F$338,SMALL(IF('2013'!$E$3:$E$338<>"",ROW('2013'!$E$2:$E$338)-ROW('2013'!$E$2:$E$338)+1,ROWS(B$1:B1)),""))
I have been using this macro to separate lots of rows with a certain amount of blank rows.
Example:
Code:
Sub test()
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A2")
[Code] .......
I trying to change it so that, instead of inserting how ever many blank rows, it just copys the row above.
Example (Row A to C)
I have been messing around with macro recorder and i could select each row and paste it into the blank rows, but from researching on the internet selecting and pasting data seems to be a waste of resources (ram?) and i will be running this on a couple hundred rows (lots of data). Also, i would like to keep the functionality so that i can still choose how many times it copys/inserts each individual row from a input box.
I eventually want the data to end up like it is shown in the table but i am takin it one step at time because i want to understand what the code is doing.
i have this code which inserts blank rows in alternate rows,
Code:
Sub insertrow()
' insertrow Macro
Application.ScreenUpdating = True
Dim count As Integer
Dim X As Integer
For count = 1 To 20
If activecell.Value "" Then
activecell.Offset(1, 0).Select
[code].....
what changes should i make in this code to insert rows only when ther are now blank rows. So first time i run, blank rows are already there, and when i update some data at the bottom and re-run it inserts blank rows again.
I have this in Column A, with about 120 entries:
Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1
Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2
Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
Using excel's text to speech I've put together a basic spreadsheet.
[URL]
Code:
Function talkit(Speech)
Application.Speech.speak (Speech)
talkit = Speech
[Code]....
I have the following codes to delete all blank rows in column A
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow
With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With
End Sub
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
View 9 Replies View RelatedI need a hand with conditional formatting if possible?
In the attached document i need the cell fill for the cells containing values (in column C) to change if data on sheet 2 is anything other than blank.
e.g. Sheet 1 C3 would be green fill and C4 would be no fill as Sheet 2 B3 contains a date and B4 does not
I have four columns of data, the last column being Date Completed, I'd like to shade the whole row of four cells if the last cell contains a date. I tried to just work on shading if the cell contains anything with conditional formatting using the formula:
=$D$1"" apllied to all my cells and chose green shading and nothing happened.
Here's my problem: I want conditional formatting to show yellow on dates for this month, lite red for cells containing dates this week, and red for any past dates. I figured out all of that, now IF there is a date in the cell to the right, the cell turns green. In other words the cell to the right shows that the action was completed on that date and we are good.
What would the formula be to:
Turn cell green, IF cell to right is NOT BLANK
I have a huge number of excels that are formatted poorly. They basically look like a checkerboard of data. Looks similar to the illistration below. The X's represent Data and the periods blank cells.
X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
. . . . . . . X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
. . . . . . . X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
. . . . . . . X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
. . . . . . . X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
Except that this goes on for over 100 columns and 30,000 rows. Each block of data is about 4 to 6 sheets printed. My boss does not want to get any blank sheets, only sheets with data on them.
I can see the pages outlined after I Print Preview. But I really don't wat to Ctrl+Click and set the print area thousands of times. Is there a macro that can do this?
I was thinking something along the line of check the range of cells for each page, and if the page has data in it, then set that page's cell range in Print Area.
I have a totals spreadsheet that links many other spreadsheets. I need to set up a formula for conditional formatting if the cell is blank (NOT ZERO). I need to ensure that the users are inputting zeros and not leaving the cells blank. I tried this but it's not working: ="IF('[Brown Deer-Q2-08.xls]Week 7'!G13)<"""
View 3 Replies View RelatedI've read dozens of threads on how to have conditional formatting ignore blank cells but I am not understanding how to make it happen myself...
Logic: Highlight the cells that are >=10 but ignore the blank cells that have functions in them
I have tried to set up a list of cells to highlight in red any numerical values which are greater than 0 using conditional formatting. This works fine, except that all blank cells are also highlighted in red. Formula is currently: cell value is greater than 0. What do I need to do to ignore the empty cells?
View 2 Replies View RelatedI am looking for a macro that will copy the row above it, to include formatting and formulas, however not the actual contents. I need the row that's being copied to be hidden from view, which is where the problem stems from. The macro is also copying the 'Hide' formatting and I want to exclude that. This is the current macro I am using:
[Code] .....
I need to use conditional formatting to recognize blank cells meaning totally blank and not cells with formulas returning 0 what i must use to get this result?
View 9 Replies View RelatedOk so my project is tracking how many days have passed since a collection notice has been sent. Assuming the case isn't closed, a response from the customer hasn't been received, the case has been assigned to an employee, and assuming the notice was actually sent in the first place.
There are instances where the collections case may have been closed without a response date or any other date and we call that "Administratively Closed."
This is my formula: =IF(OR(CaseStatus="Closed",ISBLANK(NoticeSentDate), ResponseDate""),"",TODAY()-NoticeSentDate)
Problem is, now that I'm trying to conditionally format the ones >60 days, and =45 days WITHOUT getting the "blank" cells to change color too.
I got the one for >60 days: =AND(OR(CaseStatus"Closed", CaseStatus"Unassigned"), Comments "Administratively Closed", DaysPassed>60, ResponseDate="", DateEnteredInDatabase"")
I can't get =45 without having a bunch of cells that appear blank change color too.
I have the code below that clears colums B-J and L-N in whatever row you activate a cell in. When a row gets cleared, how do I shift all the other rows up to eliminate blank rows in between the data real-time? I've deactivated the cut function already since this messes up references in the worksheet, so it would have to only use copy, paste, and clearcontents functions. Also, I don't want to DELETE any rows, just essentially shift the blank rows to the bottom. So when a row is cleared, all others shift up and the blank one goes to the end of the data that is available for data entry, so rows 17 to 116 are always available. But it must check to make sure that B-J and L-N are ALL blank, otherwise some wanted rows with one piece of information might get cleared. So at any given time, my range will always go from row 17 to 116. This is Excel 2007. Here is the code I have to clear rows that can be built upon.
View 14 Replies View RelatedI have an excel sheet that has the A column populated with many different things.
I'm not sure how possible it is, but I'm trying to group everything up by 7s and putting a space in between the groups.
So...
ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7
HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7
QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7
Would turn into
ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7
HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7
QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7
I have a spreadsheet where I have a column of dates and I want conditional formatting to highlight the cell red if the date is less than or equal to today but if the cell is blank to do nothing.
At the moment I have the following formatting applied using the "format only cells that contain" option I have cell value less than or equal to =TODAY() except that obviously highlights every cell red that doesn't contain a date. Is there another conditional format I can apply in addition to this that will not highlight the blank cells ?
I want AB1 to highlight red if date in S1 is not blank and N1 doesnt equal AA1. Is this possible?
View 1 Replies View RelatedAs a pert of a spreadsheet I have 2 columns (M5:N400) of 400 cells deep. Each cell in these columns contains a formula that counts days, when appropriate. They can end up showing a value of blank, a number between 0 and 5, or any number greater than 5. (But very rarely greater than 30)
If the number is greater than 5 then it means that it has exceeded it's target time and I wish to highlight this with conditional formatting (CF). I have a CF on all cells in both columns that says.
If "The cell value" is "Greater than" "5"... then
format the background colour to a pale yellow.
It works, but doesn't work.
If the cell value is 0 to 5 it's unchanged. Good.
If the cell value is >5 it changes. Good.
BUT, if the cell is blank it also changes. Bad!!!
I assume it is because it is looking at the fact that there is a formula in the cell, even though the displayed value is "". So I tried a second level saying that if the cell is equal to "" then no format, but it still changes.
I have a formula in A56 that adds cells A1:A54. I want to use conditional formatting to highlight A56 if any of the cells in A1:A54 are blank. I am unsure how to do this.
View 3 Replies View RelatedI'm trying to apply conditional formatting (shading) to cells that are left blank.
(Purpose: I am designing a research template for a client to complete with data and want the spreadsheet to show them where they've "missed a bit"!)
(When I go to the conditional formatting box, it asks me to specify when "cell value is"..."between/not between/equal to/not equal to" etc. But there's no option to specify when the cell is blank.)
I need to format cell D1 to have the fill color be red if any cell in D2:D21 is blank. I've tried
=VLOOKUP("", $D$2:$D$21, 1, FALSE) but that returns #N/A (there is one blank cell in the range at the moment).
I really don't want to use =OR($D$2="", $D$3="", ...$D$21="") if I can avoid it.
I have a range of cells with numeric values or blanks. I set up a Conditional Formatting rule with this range selected: New Rule > Format only cells that contain > Format only cells with: Blanks; then I set the formatting to a light green fill and on the Number tab > Category = Custom > Type: "blank"
The blank fields get the light green fill, but no text; that is, the value displayed is still blank.
I go back to edit the CF rule and change Blanks to No Blanks. The results are what I expect: the cells with numbers display the text blank and have a green fill; the blank cells have no fill (white).
This is my testing criteria. I eventually want the empty cells to display 0 (zero). I tried setting Custom > Type: 0 (the number placeholder zero) and Type: "0" (literally the number zero), and neither works.
I have tried this with Format only cells with Cell Value equal to [the address of a blank cell].
I tried to set the value as "" but kept getting =""""; and ="" became ="=""" ???
I have tried this with Use a Formula... using the len()=0, isblank(), and other approaches, all with the same results.
I also made sure the option [x] Show a zero in cells that have a zero value is checked.
With all approaches to identifying the blank cells, they ARE obviously being recognized as blank values: the fill color is being applied to the right cells; and when Not Blank is the criterion (or a negation of a formula), the blank ones are not formatted. But blank cells will not display the text as defined in the Custom Formatting.
And this isn't an issue of "If the cell displays 'blank' it's no longer blank, so the rule doesn't apply" - HERE'S WHY: I set up a second CF rule that sets the Font to red when the value is greater than zero and had this CF rule follow the CF rule for No Blanks (the test above that works). Cells with positive numbers displayed blank in red, cells with zeros or negative numbers displayed blank in black. This clarified that the actual value of the cell is being evaluated, not the displayed value after Custom formatting is applied.