Hello all

I have a spreadsheet which I need to advanced filter, but it doesn't arrive in quite the right format to do it automatically.

I firstly need to be able to delete a row if it satisfies criteria i.e. if the row is blank and is beneath a row where "England" is the value in the first cell OR (and this bit completely flummoxes me) if the row is above England and is blank for the first few columns but in column H or I (for eg but it may change slightly) will contain the value "£'s". Or would it indeed be better to insert a couple of rows and enter headers for the filter there?

I suppose the other thing I need to do - that is delete a column if it is completely blank - could be based on similar code to what would satisfy the above?

I have attached a sample file which should hopefully make what I'm looking at clearer.

Thanks in advance for anyone's kind help!

I have a spreadsheet which I need to advanced filter, but it doesn't arrive in quite the right format to do it automatically.

I firstly need to be able to delete a row if it satisfies criteria i.e. if the row is blank and is beneath a row where "England" is the value in the first cell OR (and this bit completely flummoxes me) if the row is above England and is blank for the first few columns but in column H or I (for eg but it may change slightly) will contain the value "£'s". Or would it indeed be better to insert a couple of rows and enter headers for the filter there?

I suppose the other thing I need to do - that is delete a column if it is completely blank - could be based on similar code to what would satisfy the above?

I have attached a sample file which should hopefully make what I'm looking at clearer.

Thanks in advance for anyone's kind help!

- Delete entire row from range based on criteria
- Adding data in columns based on criteria in more than one column..
- Move cell value to left column based on Criteria
- How to sum up costs over multiple rows and columns based on year-to-date
- Conditionally Hide Rows Or Columns
- Copy cells in one row to another based on criteria in target row
- Conditional hiding of entire rows
- Conditional Formatting to Hide Rows or Columns?
- Count no. of nonblank cells in one column based on criteria of ano
- Macro to delete entire rows when column A is blank ...a quick macro
- Excel VBA - Autoadding new row and column based on user input
- Determine if entire row or column is selected
- Using Variables in Ranges to select entire rows or columns
- Conditional Formatting to Hide Rows or Columns?
- Deleting nth row or column
- Count Unique Occurrences in one column based on criteria another column
- Summing a Column Based on Criteria From Another Column
- Delete Columns Based On Criteria Of Conditional Formatting
- Copy Rows To Columns Based On Criteria Of First Columnn
- Copying Rows of Data Based on Criteria
- Delete rows based on values retrieved from cells stored in another sheet
- Return multiple rows of data based on criteria
- Fill cells with color based on criteria in two cells
- Macro to show/hide rows based on a range of dates

Any ideas would be great

Thanks

address my needs. Here is a fuller, and I hope, clearer explanation. How do

add the figures in one or more columns based on criteria in more than one

column? For example assume the following worksheet:

A B C D E

1 aaa n n n

2 bbb zzz n n n

3 aaa yyy n n n

4 ccc xxx n n n

5 aaa www n n n

6 bbb n n n

7 bbb zzz n n n

8 ccc n n n

The real worksheet has hundreds of rows. I would like to add up the n's in

the rows that have a specific criteria, e.g. aaa in Column A and bull

(blanks) in Column B. I would also like to add up the n's in the rows that

have a specific criteria in Column A and anything in Column B (not null or

blank), e.g. aaa and yyy with the aaa and www. There are too many different

strings in Column B to use specific criteria. Any help would br greatly

appreciated. Setts

Hi,

I am trying to do the following and have tried Hlookup with If statement but can't get this to work. I Input 1 value (say 3000) in any cell between Column k37:V37, I would like this number to enter in same row to corresponding columns AA37:AL37 based on following Criteria in Column X37 if X37 =7 (then enter K37 to AA37) like for like - apr -apr if X37 =30 (then enter K37 to AB37) Apr(k37) to May(AB37) if X37 =60 (then enter K37 to AC37) Apr(k37 to Jun(AC37)

Any help would be much appreciated. I'm hoping to do this with a macro if possible.

Many thanks in advance

Haz

I would really appreciate some help with a formula that can sum up Costs over multiple rows and columns based on the year to date. I attached a sample of the data I'm trying to work on.

I'm trying to do an analysis on a quarterly basis, e.g. Jan vs Mar.

For Jan vs Mar, I chose Mar in the drop down box and the Sales - Mar YTD will be shown in the Total column. For the Jan part, I used the formula "=SUM(D14:CHOOSE(E16,D14,E14,F14,G14,H14,I14,J14,K14,L14,M14,N14,O14))" with E16 showing "1" for the month of Jan.

As Sales is only on 1 row (Row 14), my colleague helped me with the above formula.

For Costs (which could be 4 rows or more), the Costs - Mar YTD is also shown in the Total column. However, for the Jan part (highlighted in red), I'm stumped. I could repeat a variation of "=SUM(D14:CHOOSE(E16,D14,E14,F14,G14,H14,I14,J14,K14,L14,M14,N14,O14))" 4 times, but is there any simpler way to do this? I'd like the option of switching up the months for analysis so a simple formula would really help.

Apologies if my question is "wordy" but I could really use some help as I'm not very Excel-savvy!!

Cells A1 thru E1 from the row to be copied (e.g., Row 3) have the customer# in cell A3.

Other data is in rows 4 thru 7.

The target row (e.g., row 8) contains customer# followed by the word "Total" in column A, and a quantity in cell G9.

This repeats but with varying number of rows of data. However, each new "group" begins with a different customer#.

If anyone can provide a macro that will help it is much appreciated.

Thanks,

I was wondering if anybody knew how to hide an entire row or column based on

a stated condition using a specific cell elsewhere on the worksheet. I know

that it is possible to use [Format] Conditional Formatting to make the cell

appear empty, but I want to hide the entire row or column so that it no

longer appears on the worksheet. Is there a way to do this with or without

using VBA?

Thanks in advance and have a happy new year.

Nick

from cells in another column. Example: Column A contains years 2006, 2007,

2008. Column B contains rank such as Lt., Col., etc. I need to know the

total number of 2006 individuals who have the rank of Lt., the number of 2006

individuals who have the rank of Col., etc.

I can get the count of non-blank cells but cannot figure out how to have it

look at the other column for the type of count I need.

please help me with this

thank u so much frnd

--

Message posted from http://www.ExcelForum.com

Since i am counting the no. of rows and columns based on the first row and column:

VB:As my program depends on the first row and column to be with data, i need to automate a commandbutton so that when the user clicks it, a new Column with heading "Key" would appear on the first column of the worksheet and a new row would be inserted at the top too with input boxes for users to input the the no. of headings at the top and each heading would also be inputed by the user. Anyone knows??CountRows() Dim rngToCount As Range, lCnt As Long Set rngToCount = Sheet2.Range("A:A") lCnt = Application.WorksheetFunction.CountA(rngToCount) MsgBox "No. of Rows: " & lCnt End Sub Sub CountColumns() Dim rngToCount As Range, lCnt As Long Set rngToCount = Sheet2.Range("1:1") lCnt = Application.WorksheetFunction.CountA(rngToCount) MsgBox "No. of Columns: " & lCnt End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks.

So if I select Column A, I can get my macro to recognize that I have done so and do a certain function.

Range ("1:1")

Range ("C:C")

How would I do the same using variables if x = 1 and y = C? Please include the declarations as I am having trouble understanding if I can use string for y.

Bonus points, how do I select Range ("A1:A2") entirely with variables?

Thank you!

I want to do a count of unique occurrences of data in one column based on criteria in another column.

I have attached a sample worksheet with the data. I want to get the result in cell I26.

Thanks.

ltsolis

VB:This doesn't seem to do anything though, and since I really don't know much about VBA I thought I'd post here for some help. Thanks!DeleteBlankColumns() Dim Col As Long, ColCnt As Long, Rng As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual On Error Goto Exits: If Selection.Columns.Count > 1 Then Set Rng = Selection Else Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column())) End If ColCnt = 0 For Col = Rng.Columns.Count To 1 Step -1 [COLOR="Blue"]If Application.WorksheetFunction.CFColor(Rng.Columns(Col).EntireColumn) = 0 Then[/COLOR] Rng.Columns(Col).EntireColumn.Delete ColCnt = ColCnt + 1 End If Next Col Exits: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have attached a worksheet to help better understand my needs.

Ex. sheet1 contains phone numbers and charges/credits associated with those phone numbers.

Sheet2 contains the column heads for the types of charges or credits and I need to combine all the charges and credits for one phone number on a combined row.

I am really a novice, and I need help trying to copy rows from one sheet into another sheet based on criteria that I have in a separate table.

Here's the story. I need to pull sales data for a bunch of reps, I then need to place each reps data in worksheets depending on which region they belong to. Each rep has about 5 or six rows of sales info that needs to go with it. One caveat is that a total row for each rep is the last line of data for each rep. The total line contains hard numbers, not formulas. That's just the way it gets pulled from our in-house application, and I can re-create the totals if need be.

I am attaching an example of what I would need to do. I would need something that reads the data on the "Original Data" tab, then compares the name in column A to the table in the criteria worksheet to figure out which region the sales person belongs to, and then outputs the rows that have that reps name in it to the appropriate worksheet. I have created a NJ tab that gives an example of what I would need to take place.

Overall, what I need to do is not terribly complicated. I am just cutting and pasting rows based upon what region a sales person is in, but there are a ton of sales reps (I have removed the majority of them to keep the spreadsheet simple), and its quite tedious and leads to mistakes doing it manually.

Any help would be GREATLY appreciated. I am not sure if this can be done with any built-in excel functions or if this requires any VBA scripting (of which I have no experience, but I am pretty tech-savvy).

Please help!

Thanks.

Jan11-Data.xls

I’ve the following data stored in a Sheet:

Sheets to be deleted: ArchiveTab, SettingsTab

Rows to be deleted:

SheetName: TestSetup; CellRange - 48:52

Basically I want to delete some rows (or sheets) based on tables like this one and I’m stuck.....

I don't want to use code like:

VB:as i've many cell ranges to delete from many different sheets and i don't want to repeat the same code all over again.Sheets("Test Setup").Select Rows("48:52").Select Range("A52").Activate Selection.Delete Shift:=xlUpIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

For deleting sheets I’ve made a function like this one (the best I could come up with ....):

VB:Any help is much much appreciated.DeleteSheet(X, MinCell, MaxCell) Dim cell As Range Dim val As String Application.DisplayAlerts = False For Each cell In Range(Cells(X, MinCell), Cells(X, MaxCell)) val = cell.Value If cell.Value "" Then Sheets(val).Delete End If Next cell End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

chosen in sheet2, I need to display in sheet2 all of the rows of data that

match the criteria. I'm having trouble with multiple criteria (i.e. for

name1, I want all matching rows of data in a certain timeframe. Not all data

in ColA is are same name and will not be in any particular order.

Sheet1

A B C D E F G H I

Bryan D Smith 2/1/06 1 2 2 6 6 1

Bryan D Jones 3/1/06 2 1 3 4 2 1

Bryan D Smith 3/5/06 1 1 1 1 1 1

Sheet 2 would return based on user selecting between dates of 3/1/06 & 3/15/06

Bryan D Jones 3/1/06 2 1 3 4 2 1

Bryan D Smith 3/5/06 1 1 1 1 1 1

I can get it to return all of the items for that match in column A but how

do I for certain dates?

Here is my formula that I'm using to get all rows that match

{=INDEX('data entry'!$C$3:$U$34,SMALL(IF('data

entry'!$C$3:$C$34=$F$5,ROW($1:$32)),ROW(1:1)),3)}

Thanks in advance for any help!

I have an excel worksheet in which there are 1000 rows. Each row has 10

columns. Based on Criteria (ex. Cell C's value =TRUE and Cell F

value="Yummy") I want to fill the entire row with one color and the format

the cell (ex. E with the bold font and red color). The conditional formatting

doesn't work because of the two criteria. Can anybody please help? I would

love to get a non-macro solution but if there isn't any non-macro, I would be

willing to try the macro stuff.

Thanks in advance

record for a single day. Column B holds the dates beginning at B2 and going

down to whatever is the last date. At least one year of data will be in the

spreadsheet but it is very likely that several years' worth of data could be

collected in the spreadsheet. I would like to have just the current day and

and a limited range of preceding days displayed by default when the

spreadsheet is opened. This could be just the current day and a fixed number

of preceding days (such as 5 or 10). Ideally, users would just see the

current day, the preceding days of the current work week, and all the days of

the previous work week. A work week is Mon.-Fri. No data is collected for

non-working days and no non-working dates are on the sheet. The spreadsheet

has been set up with dates going to the end of the current year (and this may

be extended to the end of the next year or even beyond). However, users

wouldn't need to see future days because these are irrelevant until data is

available to fill in for those dates. I would like to give users the option

to toggle a checkbox or radio button to show all dates from the current day

to the earliest date in the spreadsheet or just those dates shown by default.

I have found several examples of code to hide/show rows or columns based on

various criteria, including a date, but I am having trouble putting it all

together, particularly with a toggle for a date range (either a simple fixed

number date range or the date range I described as ideal). I would appreciate

any suggestions or help.