usedrange

Hi All, I have excel file with templates which looks something like this. I am trying to filter down rows based on customer ID column and create PDF . I am using below VBA code to do the job. Public Sub Create_PDFs() Dim CustomerIDsDict As Object, CustomerID As Variant Dim r As...

I have a workbook and the first row is used for markers of important columns. The identifier basically lets me know there is important data in that column. I have been trying to shortening my looping of cells through used range but I am weak on it. First I want to loop through the first row of...

I keep getting a Runtime Error 1004 with a particular macro. The debug option always take me back to the "UsedRange" portion of: If ws2.Range("A:A" & Rows.Count).End(xlUp).Row > 4 Then ws2.UsedRange.Offset(4).Clear What's so frustrating is that if I copy and paste the contents to a brand new...

Hi, I am struggling to do something really simple: Adding a Macro to my active workbook which opens another workbook (located in the same folder), copy all the values from one sheet and pasting it into my active worksheet. Here is my code: Sub Button1_Click() Dim filename As String Dim...

Hi experts, I have two quite simple questions: what is the statement for finding the number of rows with at least one field filled. Similar question for columns, but this time first I want to know to know the column label together with its number as integer. Secondly using these two numbers I...

I've been using this one: For Each c In ActiveSheet.UsedRange If c.Value = "" Then c.Value = "n/a" Next ... for adding "n/a" to blank cells in UsedRange. But now I'm getting into trouble 'cause of earlier use of this ws/sheet. When running code, the code applies "n/a" to cells in...

Hi Guys, working on a script to dynamically update a named range for my data to the last row when a report is ran and hitting a roadblock with my code. I am getting an error on the code, most likely because the syntax is wrong so I was hoping someone may know how to do this. I am using the...

A simple explanation of what I am trying to accomplish is this: We have a .csv file that has a bit of post processing done on it. We take these 2 files and put them in 2 separate sheets within the same workbook in excel (done through VBScript). I then create a 3rd sheet and my goal is to...

I'm sure this is quite a simple question. I want to find a way to use the UsedRange function in conjunction with a loop. Currently I am using the following code but I am having to manually input the value in Z to the bottom of the table. How do I replace this with the UsedRange function? I'm...

Hi, I have a worksheet called DATA in Excel files (called Master) where I load data from others excel files. Sometimes I need to load a huge amount of data (about 10.000.000 cells) but the most of times I just need about 100.000 cells. The point is that once I loaded a big amount of data...

Excel 2016 Looking for some code that will create a summary table in Columns D/E, at the end of USEDRANGE (plus one row). The table would summarize the number of unique values in column A of USEDRANGE. Here's how I'd like for the summary table to look (keep in mind, the number of unique...

Hi: I am trying to write vba code that checks each used cell in a specific worksheet and changes its format to numeric, with zero decimal places. However, I want the code to skip the contents of 3 columns, as these contain dates or strings. My code is as follows: With...

Hi, My named ranges are dynamically set, but does not match in size because of blanks in one column. I have not been able to track VBA down to change blanks to zero's in only one column WITHIN the used range which I called "MyRange" (I used VBA to dynamically extend as rows are added. I have a...

Hello, I am trying to do a custom sort on a UsedRange. The table is dynamic, but I will always want to sort on Column E. I tried the code below, but it is throwing an error. Sub Macro3() ActiveSheet.UsedRange.Sort Key1:=Range("E1"), Order1:=xlAscending, _...

Hi, I have a code that goes through various sheets within the workbook and clear unprotected cells. Which have been working perfectly fine until I upgraded to Excel 2016. Frustrating and annoying as could not find the reason. Code is below; Dim wks As Worksheet On Error Resume Next...

Activesheet.usedrange.columns(1).cells.value = "SomeValue" The above code enter value only for the filtered cells. How to enter a value for all the cells in the columns(1) of the Usedrange while the range is filtered? P.S: I don't know the filter criteria. Kindly advise if there is a method...

With .UsedRange .Select .Columns(.Columns.Count).Offset(0, 1).FormulaR1C1 = "=countA(RC[-" & .Columns.Count & "]:RC[-1])" .Rows(.Rows.Count).Offset(1, 0).FormulaR1C1 = "=countA(R[-" & .Rows.Count & "]C:R[-1]C)" The newly added row and column are not getting included in the Usedrange. How to...

Here is a piece of my code: Basically, I am trying to get UsedRange from one workbook and use it as a range in VLOOKUP statement. 'usedRangeDestination.Select For i = 2 To FinalRowMain strRecipient = Application.VLookup(wsMain.Cells(i...

Hi friends, I need your help, below are the things which i am trying for my code to perform. • Copy data from range B5:F13 (range will not be fixed) • Go to sheet “Archive” paste copied range as value from cell A3 • Delete duplicate rows from sheet “Archive” (Like in the sample file last two...

Hello, Really not sure what is happening, my excel sheet says (through the vba code ActiveSheet.UsedRange.Rows.Count) that my last used row is the row 1048576. I have selected all beyond the scope of what I'm actually using, selected 'clear content', and it still says my last used row is...