Free Microsoft Excel 2013 Quick Reference

Delete entire row or column based on criteria

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!


Post your answer or comment

comments powered by Disqus
Is it possible to delete entire rows between rows 4 and the last row of data where columns F & G are either both blank or both zero? I have already define the lastrow of data as the variable "lastrow".

Any ideas would be great
Thanks

I asked this before but my explanation was labored and the answer didn't
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

Move cell value to left column based on Criteria

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

Good day everyone!!
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!!

I'm currently using "Excel 2002 SP3." I use conditional formatting in many of my spreadsheets, but I haven't been able to figure out how to "Hide" a row or column based on a certain condition. Is there a method for doing this already built into Excel's functionality, and if not, is there a way to do it with VBA? Any help would be greatly appreciated! Thank you.

I have a spreadsheet wherein I need to copy data from specific cells in one row to another based on criteria in the target row. For example:
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,

Hi All

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

I'm currently using Excel 2002 SP3. I use conditional formatting in many of my spreadsheets, but I haven't been able to figure out how to "Hide" a row or column based on a certain condition. Is there a method for doing this already built into Excel's functionality, and if not, is there a way to do it with VBA? Any help would be greatly appreciated! Thank you.

I need to count the number of nonblank cells in one column based on criteria
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.

a macro which can quickly delete entire row when column A is blank

please help me with this
thank u so much frnd

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

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

	VB:
	
 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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??
Thanks.

Is there a way I can determine if an entire row or column is selected?

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

I know it's possible to set these 2 ranges to capture an entire row or column:

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'm currently using Excel 2002 SP3. I use conditional formatting in many of my spreadsheets, but I haven't been able to figure out how to "Hide" a row or column based on a certain condition. Is there a method for doing this already built into Excel's functionality, and if not, is there a way to do it with VBA? Any help would be greatly appreciated! Thank you.

How can we delete nth row or column, or data in them ; without actually going to the cell & deleting it.

Hi

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

I'm trying to find a way to sum a column based on criteria in an adjacent column. In this case, I have a column that lists lengths of pipe with the location of the pipe listed in the adjacent column. I want to be able to sum up lengths based on the location criteria. My knowledge of Excel functions is fairly basic and I would appreciate any help.

Hi everyone, I'm new to VBA and I'm trying to write a macro that will delete columns based on conditional formatting. Basically what I have is a range of cells, each cell has 2 conditional formatting criteria, both with the same conditions and if this condition is true, then the cell is highlighted yellow and if the condition is false, the text is white colored. What I want the macro to do is for each column in this range, check the CF of each cell, and if every cell in the column meets the second CF criterion (ie., the text is white colored), then delete the entire column. I've found some VBA code online for a function that checks for CF, and I'm trying to use that with some code I found for deleting blank columns. Here's what I have so far, with the line I modified in blue:


	VB:
	
 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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!

I have worksheet (Sheet1) which has rows of data. Sheet2 contains column headings that are indentical to cells in rows on sheet1. I need to move the data from the rows on sheet1 to the columns on sheet2 that match the criteria in column A on sheet1.

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.

Hello Everyone,

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ím a newbie in Excel VB so please bear with me if I ask stupid questions.
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:
	
Sheets("Test Setup").Select 
Rows("48:52").Select 
Range("A52").Activate 
Selection.Delete Shift:=xlUp 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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.

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

	VB:
	
 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help is much much appreciated.

I have two sheets. Sheet1 has multiple lines of data. Based on criteria
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!

Hi,

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

I have a spreadsheet in which users enter data each day with each row being a
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.


No luck finding an answer? You could always try Google.