rng

  1. M

    Filter, copy and paste into another sheet

    Hi, I've been creating a macro to filter on column N in the Raw Data tab and then copy and paste columns A-J into the Report Log at the bottom. It works, except theres currently one result with Check in Column N but it is copy and pasting this in twice. Any idea why? Sub...
  2. I

    Inputbox to select the range from user to apply a specific function.

    Hi All, I have following VBA code to remove extra hidden characters from a range. It works fine. But many times I need to change the range for removing extra hidden characters and for this everytime I have to go edit the macro. I want an inputbox to be appeared to select (from user) the range...
  3. S

    Macro to hide a specific row in column C with the following macro which I want to use to unhide as a toggle

    Hi all, I have a macro which hides all rows in a column within my specific spreadsheet as per the ranges used. I would like to set my macro up to toggle between hiding and then unhiding so need something to reverse the process. The following is the code I have used: Sub HideUnusedRows() Dim...
  4. I

    Deleting columns if duplicate data found in a few

    Goal: If a duplicate value is found in a row, then delete the second the column associated with the duplicate value My problem: My code seems to work for the most part, except it only deletes the FIRST duplicate values that it finds, not all duplicate values. And if you run it again it seems to...
  5. B

    ComboBox Value in VBA

    Hello all, I have the following code below, I want to be able to set some kind of autofilter on the drop down box to show only the given part number of "217230*". I tried including it in the Criteria1: field but did not work. I only want to see the part numbers "217230*" when you click on the...
  6. B

    Faster way to loop

    I would like to know if there is a faster way for a loop to run. Here is what i have now and it is slow. Dim rng As Range Dim lColorCounter As Long Dim rngCell As Range Set rng = Sheets("MASTER LINE LIST").Range("C2:Z2000") lColorCounter = 0 For Each rngCell In rng...
  7. T

    Messagebox listing going down instead of across

    The code below lists the address of all cells in a pre-defined range whose value is zero. Dim Rng As Range With ws Set Rng = .Range(.Cells(1, 1), .Cells(1,1000)) End With Dim ZeroColl As Collection Set ZeroColl = New Collection Dim...
  8. J

    VBA User Selected Range Error on Cancel

    I have been using this bit of code to get a user selected column (uses worksheet and string inputs). HOwever, if the user selects cancel, it errors out. Interestingly, it was working until I declared all my variable types. Any idea how to work around the error? Thanks, Justin Private...
  9. Nelson78

    Regex: replace the 3rd occurrence

    Hello everybody. I've been manipulating some strings. Now, I'm in a deadlock. I mean: in a specific string, I want to replace only the third occurrence of character "a" with character "b". How can I build the pattern (see in red below). Sub substitute() Dim lr As Long Dim Rng As Range...
  10. G

    VBA code not working in a Range

    Hi Guys, I have the following code to add a comment each time a cell value changes, however if I change the Range to "N25" everything works perfectly, but if I want to have the code to cover all rows in column "N" I don't get any comment appear when the value changes. Private Sub...
  11. I

    Show msgbox when combobox entries = 2

    Evening, I have a combobox where i would like to show a msgbox when the entries become only 2 left. Please can you advise Private Sub UserForm_Initialize()Dim cl As Range Dim rng As Range Dim lstrw As Long Dim lastrow As Long Dim Lastrowa As Long Dim cntr As Integer...
  12. MannStewart

    Worksheet Change code stuck

    Hi 1) Can anyone guide me as where I am writing my code wrong? I need my worksheet to do two separate things for two sets of ranges via worksheet_Change event. the top part works, but the latter part doesn't run at all, ie. the part where I need Excel to locate & go to the adjacent cell by...
  13. P

    Runtime error 91 VBA Macro

    Sub Macro1() Dim Cel As Range Dim Rng As Range Dim Local_Variable As Object Set Rng = Worksheets("Index").Range("B5:B142") For Each Cel In Rng If Cel.Offset(0, 4) <> "X" And Cel.Offset(0, 8).Value = "X" Then Cel.Offset(0, -1).Hyperlinks(1).Follow If...
  14. kelly mort

    Filtering Data Code Adjustment Needed - VBA

    I have this code here that I want to use to filter data and upload result to the listbox on a userform. So I have managed to add some pieces together and came closer, but I am left with how to trap certain errors. The data starts on row 7. Row 6 is header – I don’t want to load header into the...
  15. T

    Replace array formula with VBA

    I am trying to replace the evaluate method with "only VBA". This first piece of code is a "normal" index match, in that there is only a single criteria and so is entered simply by pressing the Enter key. a and b return the same value, as expected. Dim IndexRng As Range Set IndexRng...
  16. I

    TRIM in VBA on a Range

    i have a range with 6000 rows, i want to apply a worksheet.TRIM function within VBA to eliminate spaces in-between and on the ends... problem is... nothing happens. whats best syntax for this? thanks <code> set rng = range("a1:a6000") With rng .value = WorksheetFunction.Trim(.value) End...
  17. F

    VBA: Remove all items from Scripting Dictionary

    How to remove all from the scripting dictionary when running to separate extractions? I've read where you can use dict.removeall, but in this case, dict was not declared. Does it need to be declared somehow? With CreateObject("scripting.dictionary") For Each Cl In rng...
  18. C

    VBA code problems dealing with the order of copy

    Hello, Any help would be appreciated as I am still new and learning VBA. I was able to put this code together to allow me to mark an x on one sheet and it copy Columns A, B, D, F to another sheet. I actually need them to copy in the order A,B,F,D instead. I thought it would be as simply as...
  19. D

    Move row then delete blank

    Hi all, could someone help me mod this to delete each blank row after it has moved the data to another sheet. Currently, it works when there is only one "Y" marked row but, if there are multiple rows, it does not delete any of the blank rows. Sub Move_Closed()Dim Check As Range, rng As Range...
  20. M

    How to count rows in non-contiguous range

    I need to count the number of rows in a non-contiguous range, such as: “C13, D17:E18, F22, G25, C29:F33” If I select those cells on my worksheet using Ctrl and the left mouse button, and then run the following code: Dim Rng As Range Set Rng = Selection Debug.Print Rng.Rows.Count...

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top