CODEWITHSUNDEEP
vbaexcel
Isra Shaikh
Isra Shaikh

Reputation: 151

Running macro with a different number of rows and columns each time

So I have a tedious task of separating/filtering data and changing the format and making a new sheet.

My question is that if i record a macro for one sheet will it work for another keeping in mind that the number of rows and columns will ALWAYS change.

There's no set number i would have to manually check that if i went into vba and changed the code accordingly.

Upvotes: 1

Views: 12080

Answers (3)

Vityata
Vityata

Reputation: 43585

Depends on how do you record it. If you use ctrl+arrows in the recording you may be lucky. E.g. the following macro works on all rows, simply coloring them in yellow. independent, whether they are 4 or 400:

Option Explicit
Sub Makro2()
'
' Makro2 Makro
'

'
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

It is recorded with CTRL+Arrow, to select the last row. However, for more complicated code you should not use the recorder.

Upvotes: 2

Durgaprasad
Durgaprasad

Reputation: 323

Recording will give you some basic idea of macro execution. But if you want that code need to work in all scenarios then you need to do some customization to your code.

To find the last row there are so many ways.

Source : http://www.vbausefulcodes.in/usefulcodes/finding-last-row-in-excel-vba.php

sub lastrow()
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Msgbox lastrow
End sub


*************************************
Other ways
**************************************

Sub FindingLastRow()

'PURPOSE: Different ways to find the last row number of a range

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Ctrl + Shift + End
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

'Using UsedRange
sht.UsedRange 'Refresh UsedRange
LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

'Using Table Range
LastRow = sht.ListObjects("Table1").Range.Rows.Count

'Using Named Range
LastRow = sht.Range("MyNamedRange").Rows.Count

'Ctrl + Shift + Down (Range should be first cell in data set)
LastRow = sht.Range("A1").CurrentRegion.Rows.Count

End Sub

but you need to use the best method which matches with your scenario.

Upvotes: 1

Mr.Burns
Mr.Burns

Reputation: 700

There is a few ways to get the last row/column in a spreedsheet

Lastrow = ActiveSheet.Range("A" & Activesheet.Rows.Count).End(xlup).row
LastCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).column

This will return the last row in column A, you may need to change this depending where your data starts. The LastCol will return a number for the column (Note that .Cells goes .Cells([Row Index], [Column Index])). This is the way I get the last row/column, I find it easier to understand and can do any manipulation needed.

LastRow = Cells.SpecialCells(xlLastCell).Row
LastColumn = Cells.SpecialCells(xlLastCell).Column

This is another way to get the last row/column, this does have issues as Excel workouts the last row/column in an odd way. If you edit the last cell in the last row (XFD1048576 in my case) then that becomes the .SpecialCells(xlLastCell)s last cell.

Let me know if you need anything clarified

Upvotes: 2

Related Questions