excel

Try this Option Explicit Sub Demo() Dim ws As Worksheet Dim cel As Range Dim lastRowA As Long, lastRowB As Long Set ws = ThisWorkbook.Sheets(“Sheet2”) With ws lastRowA = .Cells(.Rows.Count, “A”).End(xlUp).Row ‘last row of column A lastRowB = .Cells(.Rows.Count, “B”).End(xlUp).Row ‘last row of column B For Each cel In .Range(“A1:A” & lastRowA) ‘loop through column … Read more

Try the next code, please (adapted to search in C:C the string occurrence): Sub TestCutSUBRowsPaste() Dim sh As Worksheet, shDest As Worksheet, strSearch As String Dim i As Long, rngCut As Range, lastRowD As Long, lastRow As Long strSearch = “POS” Set sh = ActiveSheet Set shDest = Worksheets.aDD lastRow = sh.Range(“A” & Rows.count).End(xlUp).row For … Read more

countifs(“E:E”, “jasper”,”G:G”,”S”) will give you number of instances of jasper with S as status, and similarly countifs(“E:E”, “jasper”,”G:G”,”A”) will give you number of instances of jasper with A as status solved Count in Excel using macro

Answer for your question Sub Test() Dim RangeX As Variant RangeX = InputBox(” Enter Range of rows. For Example rows 3 & 4 Means 3-4″) Row1 = Left(Trim(RangeX), 1) Row2 = Right(Trim(RangeX), 1) For i = Row1 To Row2 Cells(i, 1).Value = Cells(i, 2).Value Cells(i, 3).Value = “Values Replaced” Next MsgBox “Process Completed” End Sub … Read more

Try it like this. Public Sub MyFilter() Dim lngStart As Long, lngEnd As Long lngStart = Range(“E1”).Value ‘assume this is the start date lngEnd = Range(“E2”).Value ‘assume this is the end date Range(“C1:C13″).AutoFilter field:=1, _ Criteria1:=”>=” & lngStart, _ Operator:=xlAnd, _ Criteria2:=”<=” & lngEnd End Sub All details are here. https://www.extendoffice.com/documents/excel/910-excel-filter-between-two-dates.html solved Macro code to … Read more

Sub main() Dim hold As New Collection For Each celli In Columns(5).Cells On Error GoTo raa If Not celli.Value = Empty Then hold.Add Item:=celli.Row, key:=”” & celli.Value End If Next celli On Error Resume Next raa: Range(“a1:b1”).Offset(celli.Row – 1, 0).Value = Range(“a1:b1”).Offset(hold(celli.Value) – 1, 0).Value Resume Next End Sub 1 solved Excel Macro to Locate … Read more

Someone else was able to answer my question on another forum. Credit goes to Trebor76 from Ozgrid for the user friendly solution. This is the solution that was given to me. This was pretty plug and play. Option Explicit Sub Acessdocumentexplorer_Click() ‘The following has been adapted from here: ‘https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application- filedialog-property-excel Dim lngCount As Long ‘Open … Read more

You should use conditional formatting. https://support.office.com/en-ie/article/Quick-start-Apply-conditional-formatting-6b6f7c2a-5d62-45a1-8f67-584a76776d67 solved How to color all the rows based on the cell values

Sub HideRows() Dim i As Long Dim j As Long Dim hide As Boolean ‘loop through rows For i = 9 To 54 hide = True ‘loop in the row: B through AF column For j = 2 To 32 ‘if we found value greater then zero, then we don’t want to hide this row … Read more

Use following sub. I tested it and found working. Sub fBold() Dim UsedCell, MyRange, srcIdentifier UsedCell = Sheets(“Sheet1”).Cells(1, 1).SpecialCells(xlLastCell).Row Set MyRange = Range(“A1:A” & UsedCell) For Each intCell In MyRange If Not InStr(1, intCell.Value, “.”) > 0 Then intCell.Offset(0, 1).Font.Bold = True End If Next End Sub 1 solved How to recognize the Integer in … Read more

Using Excel.UserRange, loop through the rows and access the cells by ThisWorkBook.Sheets(1).Cells(row, col).Value. If you find the value to be null or nothing or empty string, then call your sending mail function solved Visual basic for excel or VBA