lastrow

  1. 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...
  2. M

    Copying Multiple workbooks columns into another workbook.

    Hi, Good morning. I am very new to this. I am currently trying to create my first macro with the function of copying specific columns from different workbooks (4 workbooks) to a "master" workbook. The copied column should proceed to the last row on the master file. My code is painful to look at...
  3. C

    VBA sumproduct with dynamic range

    Hi, can some one help me to make SUMPRODUCT formula with dynamic range, as below. Actual formula With Range("c2:c" & LastRow) .Formula = "=SUMPRODUCT(--(MONTH(sales!$B$2:$B$300)=$A2),sales!$K$2:$K$300)" .Value2 = .Value2 End With Required like this With Range("c2:c" &...
  4. S

    copying cells from sheet 1 to various other sheets

    Hi I have a spreadsheet which has over 1000 lines, all for varying companies. What I would like to do is create an option so when you run it, it will copy the relevant lines to their respective sheet. The code listed below is some I have seen on here, which works to a point. It will run for one...
  5. J

    VBA issue: strFormulas returning 1004 error

    Hi, I've applied similar logic to two other macros but for some reason I am unable to figure out what I'm doing wrong :crash: Sub sfortest()Sheets("Cleanup").Select Range("M2").Select Dim Lastrow As Long Lastrow = Range("L" & Rows.Count).End(xlUp).Row Dim strFormulas(1 To...
  6. C

    Add formula after index, match

    Hi friends, is it possible to add formula after index match in vba , in normal index match its working but not with vba. vba code Sub lookup() lastrow = Cells(Rows.Count, "e").End(xlUp).Row For i = 2 To lastrow Range("e" & i) = Range("b" & Application.Match(Cells(i, 4)...
  7. J

    Compare two worksheets

    Hi, I have the code below but not working want to find difference between JAN/JUL tabs both column A but not working I kow there's a difference because one has 316 rows and the other one 315 then there's one line missing in JUL tab has only 315. here is the code: Sub compare() Dim myRng As...
  8. B

    Copying Rows With Highlighted Cells Multiple Columns

    Hello, I have this code that extracts rows with highlighted cells based on which column I point to. Right now it points to column "C". My data range spans from Column C through Column N. Each time I run this code I manually change column reference. Highlighted cells can be found anywhere...
  9. J

    VBA: Append rather than copy over

    Hello I have the following code which copies from one sheet and pastes from Row 8 in the next. Each time I run the macro it copies over, I would like to change it to append the data. Any thoughts, thanks. Sub Add_to () Application.ScreenUpdating = False Dim i As Long Dim Lastrow As Long...
  10. D

    Find result in a worksheet and use to copy information in another

    Hello all, The resource feeding one of my workbooks has changed. The source now puts an entry everytime it is entered in the database. This results in many lines with almost identical information. I nee to adjust the logic at the start to account for this. Here is the code: Sub Workie()...
  11. M

    Why aren't my newly inserted columns being populated with the Formula?

    Hey guys! First the code: Sub MyInsertColumn() Dim wks As Worksheet Dim i As Long Dim Lastrow As Long Application.ScreenUpdating = False Lastrow = Range("A" & Rows.Count).End(xlUp).Row For Each wks In ActiveWindow.SelectedSheets With wks For i =...
  12. BlakeSkate

    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 =...
  13. sharky12345

    Find value with criteria then copy row

    I'm using this to find a value in a range subject to criteria; Lastrow = Range("A65536").End(xlUp).RowFor Each cell In Range("A2:A" & Lastrow) Set r = Sheet4.Cells.Find(What:=cell, After:=[B1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) If Not r Is Nothing Then cell.Offset(0, 1) =...
  14. A

    Cut & Paste VBA

    Hi, I am new to VBA, getting a bit of a hang of it. I found this code via forums etc. Sub Delete_Data() Dim i As Long, lastrow As Long, mydate As Date, erow As Long lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row Application.ScreenUpdating = False For i = lastrow To 2 Step -1 mydate...
  15. E

    Check 2 cells from source to master file, if match, highligh cell X in master

    Hi all, Context: I have three error-checking columns in source file, worksheet "MrExcel". If there is a Y in any of the columns, goto columns A and B. If columns A and B matches with columns A and B of master file, worksheet "MrExcel", highlight cell A1 in that master file Private Sub...
  16. L

    VBA Add FillFormat to MAcro

    This Macro fills Formulas to Last Row I need it to also fill formats as well Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("W4:AA" & LastRow).Formula = Range("W3:AA3").Formula
  17. R

    Excel VBA: For loop - Write value of row number

    The way my code currently works is like this: ws1 has multiple columns and rows of data. The first cell on every row in column A is the title. The titles are from a fixed selection (it's a drop down) which are determined by a list that is on ws2 ws2 has the list of titles, which is h3 until...
  18. F

    Minimum range of variable

    Hello, I have a "variable" (I think its called), called LastRow. And LastRow will find the latest row with data in special column. But if LastRow is empty. It will cause problems and delete information. Can I in some way set a minimum LastRow range? All help would be awesome! LastRow =...
  19. A

    Autofill skipping blank cells

    Hi I am using the below code to autofill the formula however there are blank cells in column C I want the same cells in column in S to be blank... Below code is inserting the formula in all the cells.. Sub insert() Dim LastRow As Integer Range("S:S").EntireColumn.insert Range("S1").Value =...
  20. H

    Search Left

    Hi - I'm using this code as a vlookup. Is there a way to alter the code so that i'm only looking up the first 3 digits of the value in Column 6 on Sheet6? Dim LastRow, LR1, LR2, LR3, i As LongDim Searchfor, j, inarr As Variant Set wsRS = Sheet6 Set wsDT = Sheet2 With wsDT LastRow =...

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top