rng

Hello everybody, I currently have the following: Where column: I = quantity J = subtract any number entered from the quantity after which the number entered will be subtracted in column I and disappear in column J so you can enter a new one in the future J = add any number entered from the...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...