lastcol

  1. S

    Copy range from one sheet to another, error 1004

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

    VBA to paste value above

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

    Removing the extra commas in the text file

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

    insert a column after last column with data

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

    Find/Delete value from unique array

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

    Get the last column of a selection

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

    Most Effective Multi-Column Delete Row Filter [VBA]

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

    Sumif with VBA

    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 =...
  9. D

    Macro: Select Dynamic Range - but I need to offset the columns on the left by 3 columns

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

    delete columns that have an empty or blank cell

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

    How To Delete Column S to Last Column?

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

    VBA, Runtime Error 1004, Application defined or Object-defined error

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

    Macro Improvement on Execution Time

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

    VBA formatting a used range

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

    Copying/Inserting Column

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

    vlookup vba

    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 =...
  17. D

    VBA: Referring to rows as ranges

    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()...
  18. K

    VBA Delete Blank Rows Including First Row

    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 =...
  19. B

    Copy Used Range to first blank row on another sheet in same workbook

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

    Add currency to selected columns

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

Some videos you may like

This Week's Hot Topics

Top