Free Microsoft Excel 2013 Quick Reference

Trying to make macro hide row if cells contain specific text

Hi,

I am pretty new to using VBA and macros in excel. I currently have a large spreadsheet which is used for testing. I have multiple worksheets and on each sheet, there are many rows each containing test procedures and outcomes.

What I'm trying to do:
Some of the tests in each sheet only apply to certain products. In each row the test is detailed, the outcome can be recorded, and the products that the test applies to are listed e.g.

Column 1 - Test Procedure
Column 2 - Outcome
Column 3 - ProductA, ProductC etc

I need the macro to look at Column 3 and search for text relating to each product as I specify elsewhere in the sheet. I will create a button on the first sheet named as the product I want to select. This would then go through each row on each seet in the work book and hide any rows where the Product name is NOT listed. Here is what I have so far:

Sub HideRows()
Dim cell As range

Sheets("Sheet2").Select

For n = 2 To ActiveWorkbook.Sheets.Count
 
For Each cell In range("D4:D30")
If cell.Value = "ProductA" Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If

Next

I put the code in below to make the macro return to the seet I want once it reaches the last sheet in the workbook
If ActiveSheet.Name = "Sheet7" Then
Sheets("Sheet1").Select
Else
ActiveSheet.Next.Select
End If

Next n

End Sub
At the moment this code works if the ONLY text in the cells is "ProductA". This doesnt work if multiple products are listed in the cell e.g. "ProductA, ProductB".
I would also like the macro to ignore any blank cells.

I thought it would be easy but with my limited knowledge I am completely stuck.

Any help would be much appreciated!


Hi,

I am trying to format a large number of tables for pasting into a Word doc.

Is there an easy way to insert a row on a worksheet beneath any cell containing specific text - in this case, 'market summary'.

Many thanks,

Adam

Good Afternoon Friends,

I was wondering if someone might be able to assist me with using VB to insert a new row below a cell containing specific text.

For example:

- All of my data is in column A
-I want to scan all of column A, and if there is a cell that contains "ACHCAMERIGROUP M", then I want a blank row inserted below it. If column A does NOT contain that text....do nothing.

Thanks so much in advance,

Laura

how to find number of rows between cell containing max value and current cell in excel. I tried using rows function but its not working.

Thanks in Advance!!

I’m looking for a formula or way to be able to do the following:

(My example and information is shown after question)

I would like to be able enter information in column "I" and then have cell G look for that text in the list in column C. If found use text in "I" if not cell to be blank. Then I would like text to show in F depending on what is in column "I" or Column "G". If text in cell G insert text relation to what is in Cell G (If apples in G, Fruit to show in F) or if Cell G is blank search if text in Column "I" is in Column B list. If found use text in "I" and if not cell to be blank. Then I would like text to show in E depending on what is in column "I" or Column "F". If text in cell F insert text relation to what is in Cell F (If Flower in F, Market1 to show in E) or if Cell F is blank search if text in Column "I" is in Column A list. If found use text in "I" and if not to say "Error".

A B C D E F G H I 1 Market1 Fruit Apples 2 Market1 Fruit Oranges Market1 Fruit Apples Apples 3 Market1 Vegetable Carrot Market2 Flower Flower 4 Market1 Vegetable Celery Market1 Fruit Fruit 5 Market2 Plant Perennial 6 Market2 Plant Annual Market1 Market1 7 Market2 Flower Rose 8 Market2 Flower Tulip Marcket2 Flower Rose Rose

I am wondering if this could be done and if yes will it need to be a macro?

Thank you

I need a quick macro that will search column D for the words "balance forward" then delete the entire row. The macro needs to loop through many rows and it contain more than one "balance forward" reference.

Can someone help? I'm just not good at macros yet.

Thanks,
Shirlene

Hi All,

I'm looking for a function that can check to see if a cell contains specific text. For Example:

I have many cell that contain some, all, or none of the following 4-character text:
|C180|, |D410|, |S531|, |T844|
and I would like to know if, let's say D410, is contained in a certain cell. I will be checking for this in an array.

I also have another quick question. Is sumproduct or sum(if(.....)) quicker if they are both checking the same amount of criteria?

Thank you in advance,

Jason

Hi All,

I'm looking for a function that can check to see if a cell contains
specific text. For Example:

I have many cell that contain some, all, or none of the following
4-character text:
|C180|, |D410|, |S531|, |T844|
and I would like to know if, let's say D410, is contained in a certain
cell. I will be checking for this in an array.

I also have another quick question. Is sumproduct or sum(if(.....))
quicker if they are both checking the same amount of criteria?

Thank you in advance,

Jason

--
jhockstr

Hi i am creating this excel spreadsheet form for my clients to fill out. i need a macro for it to check certain cells and if they are empty then hide the row in the range. I have this up to now and it does work but i have to run the script manually myself in the VBA and not automaticly.

Any help is appreciated!

Sub HideRows()

Application.ScreenUpdating = False

Dim cell As Range
For Each cell In ActiveSheet.Range("B33:B46,B53,B63:B64,B77:B79,B100:B103,B111:B116,B136:B139,B172,B204:B215,B231:B232,B238")
If cell.Value = "" Then
cell.EntireRow.Hidden = True
Else: cell.EntireRow.Hidden = False
End If
Next

End Sub

I am trying to hide rows if any cell in a columm = 0.00. Example: If any cell
in column D = 0.00 than hide the row containing that cell.

Hi,

I've a P&L statement having many line items. I just wanted a macro which could hide rows if for e.g. if depreciation expense is 0 in row 7 from col F throuh Col T, then I want the macro to hide it, so on and so forth.

I browsed through some macros to hide rows on this forum, but did not find them suitable for my needs.

Any help will be appreciated.

Thanks,

K

Hi,

Have searched through the site looking for a way to auto hide rows if a certain cell = a condition.
I found one thread, but have been unable to manipulate the code to what I need.

Currently, rows 18 & 19 are hidden on sheet 'Qualifer'
In cell I10, I ask a question and is answered either YES or NO, though the cell is blank when opened.
If they answer YES, I would like to UNHIDE rows 18 & 19.
If they answer NO or the field is blank, I would like them to remain hidden.

Was wondering if someone could help via VBA so that the rows are auto hidden/unhidden when this cell is changed.

Thanks in advance
Tayler

I need help creating a macro to highlight entire rows if a cell in that row (C) contains specific values.

Any ideas?

Thanks in advance

Anybody can show me in Excel VBA (Excel 2003) how to delete an entire row if cell value has the word "Total", "Allocation", or "Time Stamp"? I have data in column A. How can I automatically delete rows if cells contain the words that mentioned above. thanks

Hi guys

I have tried searching for a way for VBA to recognise in VBA if a cell contains any text, but most of my searches respond to specific cases for specific characters in a string

Basically, I'm just looking for the correct way to word a string of code that if it detects any text in column B (col 2 ) then it should not do anything, else it should cut the value in col 2 and paste it in col 8

'''''''''''''''''''''''''''''Check for values in column
B''''''''''''''''''''''''''
' For i = Range("A65536").End(xlUp).Row To 5 Step -1

If Cells(i, 2).Value   "<> TEXT? this is where I need help"  Then Cells(i, 2).Cut Destination:=Cells(i, 8)


Next

Similarly, this can be rethought as determining if the cell is an integer, ie if it is an integer (and not text) then cut from column 2 and paste to column 8

Thank You

Hello,

I have to perform checks on a spreadsheet which i am trying to automate, one of the checks is to see if the first three characters of the cell meet a specific requirement.

If the cell contains "ABC123" it can be ignored and move on to the next row
If the cell contains "abc123" the row needs to be cut and placed on a different sheet
If the cell contains "Abc1234" the row needs to be cut and placed on a different sheet
If the cell contains "1234" the row needs to be cut and placed on a different sheet

Sub ABC()
Dim myLoop As Long
Dim myURange As String
Dim var1 As String

myURange = ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows.Count

For myLoop = 2 To myURange

If ThisWorkbook.Worksheets("Sheet1").Range("B" & myLoop).Text <> "" Then
var1 = Cells(myLoop, 12) = Left(Cells(myLoop, 12), 3)

If var1 = "ABC" Then
Else

Rows(myLoop & ":" & myLoop).Select
            Selection.Cut

            Set wksheetPasteTo = ThisWorkbook.Worksheets("Sheet2")
            Set rngPasteTo = wksheetPasteTo.Range("a1")
  
            Do Until rngPasteTo = ""
                Set rngPasteTo = rngPasteTo.Offset(1)
            Loop
                wksheetPasteTo.Paste rngPasteTo
End If
End If
Next myLoop
End Sub
When i run the code, every row is being copied onto Sheet2, where am i going wrong?

Thanks in Advance

I was wondering of somebody was familiar with the possibility to start
different macro's if the contents of a cell I refer to different then I want.

For example: In most cases the Cell A38 on sheet "Quote" contains "General",
if so I want to activate macro "Sub DeleteInseteredStation()" If it contains
something else however; I want it to launch "Sub ClearContentsStation()"

Does Anybody know what Macro code could be used? Thanks in Advance!

Is it possible to use a macro that merges a cell if the cell contains certain text "AWAY", I want it to automaticaly select the cells within range $B$5:$U$42. So if $B$5 contains "AWAY" I want it to merge (b5,b6,c5,c6)

Hi there

I'm using the code below to insert a blank row whenever there is a change in name in column F, but I'd like to fill the row with a specific colour for each change in name. Can anyone help me with this? It will be a different colour for each name, so what would be the best way to do this? (the colour only needs to fill the inserted blank row, nothing else)

Option Compare Text
Sub addRows()
    Dim Rw     As Long
    Dim LastRw As Long
    LastRw = ActiveSheet.UsedRange.Rows.Count
   ' loop through column F
    For Rw = LastRw To 2 Step -1
    'ignore empty cells
        If Not IsEmpty(Cells(Rw - 1, 6)) Then
        'if the cells don't match add a row
            If Cells(Rw, 6).Value <> Cells(Rw - 1, 6).Value Then
                Cells(Rw, 6).EntireRow.Insert Shift:=xlDown
            End If
        End If
    Next Rw
End Sub


Hello,
I am trying to create a spreadsheet that does the following:

If cell A1 contains the word "Inoperational" use formula "=TODAY()-B1" in cell C1. Where B1 has a date.
If cell A1 contains the word "Operational" use no formula and leave cell C1 blank.

Thank you in advance for the help.
-David

Hello,

Is it possible to delete an entire row if i have exact text in all 7 columns ?

00018175 #N/A 00018175 #N/A #N/A #N/A #N/A
00018208 #N/A #N/A #N/A #N/A #N/A #N/A

For example i want to delete the entire row that contains the text #N/A in the specific columns B C D E F G H and if one column have something else dont delete.

Hi

I'm currently using the IS(IFNUMBER....) formula to try and return an 'OK' or 'Not OK' value if a range of cells contain specific text.

If I use =IF(ISNUMBER(MATCH("Whalley AM",D5:D10,0)),"OK", "Not OK") in this example the 'OK' or 'Not OK' only shows if cells within the range D5:D10 is equal to 'Whalley AM'

I have however cause to enter additional info in cells which might be for example 'Whalley AM S' (Note the letter after the 'Whalley AM' can be anything)

On searching Excels help files I thought of using the following =IF(ISNUMBER(FIND("Whalley AM",E5:E10,0)),"OK", "Not OK") but this does not work for a range of cells. It does however work for single cell references.

I am aware that these formula's searches are case sensitive.

If anyone can point me in the right direction for what I wish to achieve I would be most grateful.

Many thanks
John

I need to create a listing.

The following cells contain:

I2: "Special Deputy/ CIT/ CERT"
I3" "Special Deputy"
I4: "CERT"
I5: ""
I6: "Hostage Negotiator"
I7: "CIT/ Instructor"

How do I get HC746 (yeah, the worksheet is huge) to read:
HC746: The value in FP2
HC747: ""
HC748: The value in FP4
HC749: ""
HC750: ""
HC751: ""

I can auotsort the numbers via other formulas so they are listed one right after the other:
HD630: The value in FP2
HD631: The value in FP4

I just need those containing this specific text (CERT).

I've tried several different fomulas, but I appear to be failing miserably, lol.

Thanks!

How do I find a cell in a row which contains a specific text string and
return a value (also a text string) from another cell in the same column?

For example: Can I search for first cell in Row 2 that contains the text
"Gate 1" and return the value "APR" from same column but different row?
Similarly, search for cell in Row 3 that contains "Gate 1" and return "MAY".

Col A Col B Col C
Row 1 APR MAY JUN
Row 2 Gate 1 Gate 3
Row 3 Gate 1

Thanks in advance,
Pam

How do I find a cell in a row which contains a specific text string and
return a value (also a text string) from another cell in the same column?

For example: Can I search for first cell in Row 2 that contains the text
"Gate 1" and return the value "APR" from same column but different row?
Similarly, search for cell in Row 3 that contains "Gate 1" and return "MAY".

Col A Col B Col C
Row 1 APR MAY JUN
Row 2 Gate 1 Gate 3
Row 3 Gate 1

Thanks in advance,
Pam