lastcol

Hello, I'm wondering how to fix that, tried many things to declare ranges within sheets but still getting 438/1004 error and can't deal with that. I'm getting error at Paste line There is last-saved code, I tried many of things just like using "With worksheet" method. Usually I'm trying to...

Hey community, I use following formula, to fill blank cells with value above. I wanna know how can i start this code from 5th row and end in 1000 row? in second column? Sub FillBlanksValueAbove()Dim sName As String sName = ActiveSheet.Name Dim ws As Worksheet Dim lastRow As Long, lastCol As...

Hello, I have this VBA code below, but the output text file has a lot of commas in it. Note that i have hidden formulas in my excel file. Most likely this is the reason. Is there a way, then when i generate the text file it will only get the rows which has data. 'Declaring the variables Dim...

I want to insert a new column (with the name) after last column with data. i tried using the below code by writing it in VBA module, however it does not work. [ Sub add_column() Dim LastRow As Long Dim LastCol As Long Dim iRow As Long Set ws = Sheet1 ' NOTE: Change this if...

My Idea: Hey Guys, so I have a Row of data, with a bunch of Sample ID's in them. I add all those values from a row into an array, then only keep the unique ones. Now I want VBA to search the array for the values in the array, if there's a match, then deletes the value from the array and also...

I want to store lastCol as a variable equal to the last column in the current selection Sub selectionEDIT() Dim sSelect As Range Dim lastCol As Long Dim lastRow As Long Set sSelect = Selection lastCol = sSelect.SpecialCells(xlCellTypeLastCell).Column lastRow =...

what would be the most effective way i could filter every column for the same criteria? so far i can think of the below sub i made, but it doesnt seem to be that efficient if multiple columns contain the criteria. If you could help me to understand another method i would greatly appreciate the...

Hello everybody. I'm running on empty with the following matter. Writing this in a cell it works fine: =SUMIF(H19:H1891,"black",G19:G1891) Now, in my VBA code: lastcol + 1 = column in which you can find the values to sum lastcol + 2 = column in which you can find the criteria criteria =...

I have the following macro selecting a dynamic range in worksheet. I need it to select the dynamic range, but without the last three columns. What line would I use, and where to offset like this? Sub ProcessData() 'Dynamic Range Selection Application.Calculation = x1CalculationManual...

What is a faster way of deleting columns that are empty? I am looping thru the first 200 columns to see if each column has data in a specific row. If it is blank, then I want to delete that column. My way takes about 20-30 seconds to run. lastCol = 0 For i = 1 To 200 If...

I have this code and its not working, can anyone explain to me why? sub test() Dim lastCol As Long with activesheet lastCol = .Range("A1").SpecialCells(xlCellTypeLastCell).Column Columns(17 & ":" & lastCol).Delete Shift:=xlToLeft end with or sub test() Dim lastCol As Long with...

Hello, I'm a beginner in need of some help please (Excel 2013). I have the below code which loops through the first sheet (Input) taking the value on the 1st row and the value on the first column where there is an intersecting "X" and pasting them into the second sheet (Output). The code...

Hi all, I have made a macro at work to basically calculate the maximum length (of a cell) for each column in a table with header row 1 (used for SQL truncation purposes... I.e. "Job Title" may have type nvarchar(20)) The macro works all well and good but it can be pretty slow when dealing with...

I recorded this macro to format a sheet in my project. The sheet is dynamic so I added LastRow and LastCol references. How do I change the code to format the used range? Sub Macro1() With ActiveSheet LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row LastCol = .Cells(1...

Hi, I'm using the following code to insert a new column at the end of existing columns. What I was hoping to do is prior to inserting the new column, it would copy what's currently the last column and either insert a new copy of that column at the end OR first insert the new column then paste...

Hi All, I'm trying to pass the value of lookCol in the lookup formula here but getting an object defined error here. Can someone please help me with this lastCol = Cells(1, Columns.Count).End(xlToLeft).Column lookCol=0-lastCol Cells(i, curCol).Value =...

I am trying to loop through all cells in a row (row 7 starting at column E to last used column) and copying the value in the cell below (row 8) if cell in row 7 does not equal zero. I am not sure if I've written the code correctly for this. Would appreciate any advice. Sub SummarizeData()...

I'm referencing a code that was posted here: https://www.mrexcel.com/forum/excel-questions/983742-vba-delete-blank-rows-formula-but-has-no-value.html Sub deleteBlankRows() Dim lastRow As Long, lastCol As Long, i As Long, myBlanks As Long With Sheets("Sheet1") lastRow =...

I'm getting a: "Method 'Range' of object_'Worksheet' failed" error message on the following code. it works up to the .Copy portion but I'm definitely doing something wrong with the Destination. Any help would be appreciated! Private Sub CommandButton1_Click() Dim mySheetW As Worksheet...

Hi All I have to add the currency format e.g. £1234.56 to those columns with Cost as part of the column headings in row 3. Once the row 3 has a word 'Cost' in the heading then the whole column from row 4 onwards will be in currency format as £1234.56. Start from column to the last unknown...