Posts by meetmoras

This is not working

Sub MisRec()
Dim ws As Worksheet
For Each ws In Worksheets
       ws.Cells.Find(What:="abc", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    ActiveCell.Offset(-2, 0).Select
    Range(ActiveCell, "A2").Select
    Selection.EntireRow.Delete
Next ws


End Sub

I have 3 codes in the attached workbook, but its not running to all worksheets

Sub formatFinal3()


        Dim WS_Count As Integer
        Dim I As Integer
         
        WS_Count = ActiveWorkbook.Worksheets.Count
         
        For I = 1 To WS_Count
            Cells.Find(What:="abc", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
            ActiveCell.Offset(-2, 0).Select
            Range(ActiveCell, "A2").Select
            Selection.EntireRow.Delete
        Next I
End Sub

This works perfect with exact match.

How can we modify this to filter with criteria contains some text

Thank you!

attached sample file

Not filtering all values - only one first value gets filtered

1. based on filter criteria array in the same sheet

Sub FilterTeams()


    Dim LastRow As Long
  
    LastRow = Range("A" & Rows.Count).End(xlUp).Row


    ActiveSheet.Range("A1:H" & LastRow).AutoFilter Field:=3, Criteria1:="=*" & Range("Q1") & "*"
    
End Sub

2. 1. based on filter criteria array in other sheet

Sub Filter()
    Dim Criteria As Variant
    Criteria = Worksheets("Sheet1").Range("A1:A140")
    Worksheets("AP").Range("$A$1:$h$100").AutoFilter Field:=3, Criteria1:=Criteria, Operator:=xlFilterValues
End Sub

Hi,

I have a list of data and from the ActiveCell I want to select till row 2 and delete the rows. (ActiveCell will be in Column A)
- I have blank rows in between in all columns
- ActiveCell will be Dynamic in each Sheet

Need help to resolve

ActiveCell to Row 2

I have below code

Range(ActiveCell, ActiveCell.End(xlUp)).Select
Do
Loop

Re: Column Numbering

Thanks much, I am very happy with the code. this is greate

Range("A3").Select    
    ActiveCell.FormulaR1C1 = "=(IF(RC[3]=""BLUE"",R[-1]C+1,R[-1]C))"
    ActiveCell.Copy
    
   
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.End(xlToRight).Select
   
    Range(Selection, Selection.End(xlDown)).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -3).Select
    
    Range(Selection, Selection.End(xlUp)).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Quote from Carim;787754

Hello,

Thanks for your file ...

In Cell J3, you can test following formula :

Code

=IF(M2="BLUE",J2+1,J2)

See attached ...

Display More

Re: Column Numbering

Quote from Carim;787565

Hello,

In order to help contributors to design a tailor-made solution ... why don't you attach a sample workbook to your next message ...

HTH

forum.ozgrid.com/index.php?attachment/71726/

I am attaching a table for reference.

Hi,

I am trying new code to number a column based one each change in other cell value.

Example,

Column A Column D
RED
GREEN
BLUE
RED
GREEN
BLUE

The first 3 rows in column A should be 1 and next 3 rows should be 2 and so on as long as the column D value .

So every after blue in column D, the next row cell in column A should increase by 1 (like +1) till the end of column D.

Thanks much

Re: Run the code for all excel sheets

Sub Hiderowforme()


         Dim WS_Count As Integer
         Dim I As Integer




         ' Set WS_Count equal to the number of worksheets in the active workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count




         ' Begin the loop.
         For I = 2 To WS_Count
            For Each c In Range("A7:A20")
            If c.Value = 0 Then Rows(c.Row).Hidden = True
            End If
                                      
        Next I




      End Sub

the code should start from sheet 2

Re: Run the code for all excel sheets

I am not getting whats wrong in the above code ... trying my best

Re: Run the code for all excel sheets

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ActiveSheet.Select
Columns("A:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next ws
'
End Sub

For Each ws In Sheets
If ws.Name Like"*_VARIABLES"Then
ws.Columns("A:D").Copy
ws.Columns("A:D").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If
Next

'I want to run this code for all worksheets from worksheet name "Template". I am not very sure on this ..

"there are couple of worksheets before the WS "template" but i want the code to run only next sheets

Re: Filter Values per user Input

Done !

link to cell

ActiveSheet.Range("$A$1:$H$30").AutoFilter Field:=2, Criteria1:=Range("M1")

Hi,

I have modified a code to filter data based on the input box, but the Macro is not giving output as expected.

I dont see an input box pop up to enter the value

please help

thanks

ActiveSheet.Range("$A$1:$H$30").AutoFilter Field:=2, Criteria1:=inpubox = "Please enter the area"

Re: Import Data from Excel to Access

I am attaching a screen shot for your reference

[ATTACH=CONFIG]59204[/ATTACH]

Hi All,

I use TransferSpreadsheet Macro to import data in Microsoft Access from a Excel worksheet. Here the path we give remains constant.

Now,

I need a solution for, when the user run the macro - it should ask the user to select the file instead importing from default link.

Please let me know if any one have any idea on this.

Thanks much,
NM

Re: Split the rows in to multiple Sheets from a master WS

Thank you Works fine

Re: Split the rows in to multiple Sheets from a master WS

Hi,

nuber of rows on the master worksheet is not fixed, it may be 10,000 or 2,000 (dynamic)

But the Split requiered will be 800 or 850 rows

master worksheet contains headers, not not necessary for worksheets

Thanks