VBA- 4 Ranges, Deleting Inserting Rows Columns

Ranges, Cells

Ranges can be reffered using  Sheets.range property

dim r as range

set ws= thisworkbook.sheets(“Sheet1”)

set r= ws.Range(“A2”)

another way of doing that is cells property .

set r= ws.cells(2,1) or   cells property has row and column options

set r= ws.cells(2, “A”)  ‘  ‘here 2 is 2nd row and column A

 

 


Copying Ranges

Method1
range(“A1:C2”).copy destination:= range(“d4”)
range(“A1:c2”).copy
Sheets(“Sheet2”).paste
range(“A1”).copy destination:= range(“D2”)

Method 2

‘Range(“m4”).PasteSpecial xlPasteValues
Range(“M4”).PasteSpecial xlPasteformats
Range(“M4”).PasteSpecial xlPasteFormulas
or activesheet.paste

Copying Data in New workbook

dim wb as workbook

Range(“A1:D1”).copy

set wb= workbooks.add

activesheet.paste
wb.SaveAs “F:\newwb.xlsx”  ‘save new workbooks as newwb

Copying only values to new range in new workbook 

Dim wb As Workbook
Range(“A2:a9”).Copy
Set wb = Workbooks.Add  ‘ create new workbook and set reference to it
Set ws = wb.Sheets(“Sheet1”) ‘set reference to sheet1 of new workbook.

ws.Range(“A1”).PasteSpecial xlPasteValues
wb.SaveAs “G:\newww.xlsx”
LAST ROW, COLUMNS
lastRow = Cells(Rows.Count, “A”).End(xlUp).Row
‘lastRow = Range(“A1”).End(xlDown).Row
lastRow= range(“A223”).end(xlup).row
‘lastColumn = Cells(1, “J”).End(xlToLeft).Column
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Selecting Ranges

Range(“A1:A10”).select
Selcting ranges with variables.
=range(“A1:A” & lastrow).select
range (“A1:C” & lastColumn).select

INSERTING ROWS

Rows(5).Insert
Range(“A1”).EntireRow.Insert

INSERTING MULTIPLE ROWS
Range(“A1”).EntireRow.Resize(2).Insert
Rows(5).Insert
Example Insert single row where a text “sum” if found (this will insert row above sum)
For Each c In ws.Range(“A1:A8”) If c.Value = “sum” Then
c.EntireRow.Resize(2).Insert
Exit For
end if

INSERT TWO ROW BELOW “SUM”
c.offset(1,0).entirerow.resize(2).insert

WITH LOOPS
For i = 1 To 14

If Range(“D” & i).Value = “column” Then
‘Range(“A” & i).EntireRow.Insert
‘Range(“A” & i).Offset(1, 0).EntireRow.Resize(2).Insert
Range(“D” & i).Offset(, 1).EntireColumn.Resize(, 2).Insert
‘Exit For
End If

INSERTING COLUMNS
range(“A1”).entirecolumn.insert
INSERTING MULTIPLE COLUMNS
range(“A1”).entirecolumn.resize(,2).insert
EXAMPLES: Insert single COLUMN where sum if found (this will insert COLUMN TO THE RIGHT)
For Each c In ws.Range(“A1:A8”)
If c.Value = “sum” Then
c.EntireColumn.Resize(, 2).Insert
Exit For
End If
inserting/ DELETING with a range
‘Range(“C1:C4”).Insert shift:=xlDown
Range(“C1:C4”).Insert shift:=xlToRight
Range(“C1:c4”).Delete shift:=xlUp

DELETING ROWS AND COLUMNS
Columns(“A:A”).EntireColumn.Delete
Columns(“A:B”).EntireColumn.Delete ‘ this will delete the Columns A to Z
‘Columns(“A:B”).Clear ‘ this will clear the Columns A to Z
‘DELETE COL A & b =Range(Cells(1, 1), Cells(1, 2)).EntireColumn.Delete
Rows(5).Delete
Rows(“5:10”).Delete

DELETING ROWS COLUMNS
range(“A1”).entirerow.delete
Range(“D1”).EntireColumn.Delete
Columns(“A:A”).EntireColumn.Delete
‘DELETE COL A & b =Range(Cells(1, 1), Cells(1, 2)).EntireColumn.Delete

offset
The OffSet() returns the value of the cell, at an offset that you’ve specified. The syntax of this function looks like this:

OffSet(Cell reference, rows, columns)
Here’s what each parameter means

Cell reference: refers to a single cell or range of cells.
Rows: specify the number of rows away from the given cell. If the value entered is negative, the given cell shifts to the left.
Columns: specify the number of columns away from the given cell. If value is negative, the cell shifts up.
It’s possible to specify just the rows and not the columns. Here’s how you can do it.

Range(“B1”).Offset(1).Select
Here we’ve omitted the column parameter and its comma. This code results in only moving 1 row down from the original cell location. It will select the entire row, not just a single cell.

How to Specify Only Columns

Similarly it’s possible to just specify the columns and omit the rows. The code will look like this:

Range(“B1”).Offset(, 1 ).Select
The comma is necessary for Excel to know that only column offset has been set.

The active cell refers to the currently selected cell. Let’s check a few examples that involve active cells. Here is the code to move one cell down from the currently selected cells is

ActiveCell.Offset(1,0).Select

Examples

Range(“A1”).Offset(5, 0).Select This moves 6 ROWS below from Ragne A1. So from A1 to A6 in same column
Range(“A1”).offset(0,5).select this moves 5 COLUMNS TO LEFT from A1.. so from A1 to F1 in same rowd

Resize

IS USED TO EXTEND A SELECTION OR RANGE
RESIZE(ROW, COLUMN) ROW OR COLUMN CAN NOT BE 0.
This will only select Row from 1 to 4 on SAME column, SINCE no column parameter is given in resize
Cells(1, 2).Resize(4).Select
This will select 5 rows down from A1
Range(“A1”).Resize(5, 1).Select IS SAME AS
Range(“A1”).Resize(5).Select

This will select 5 columns to right from A1
Range(“A1”).Resize(, 5).Select
Range(“A1”).Resize(1, 5).Select

CHECKING IF ROWS OR COLUMNS ARE EMPTY
For i = 1 To 10
If Application.CountA(Rows(i)) = 0 Then
MsgBox “empty row ” & i
End If
Next i

For i = 1 To 10
If Application.CountA(columns(i)) = 0 Then

INSERT A COLUMN WITH FORMULA

Columns(“D”).Insert

Range(“D1”).Value = “avg”

Dim lastRow As Integer

lastRow = Range(“A1”).End(xlDown).Row

Range(“D2:D” & lastRow).Formula = “=f2/e2”    ‘ value in column f divided by value in column e

DELETE DUPLICATE ROWS

Private Sub CommandButton1_Click()
Dim ar As Variant

Dim i As Integer
For i = 1 To 9

If Cells(i, 1).Value = Cells(i + 1, 1) Then
Cells(i, 1).EntireRow.Delete
End If
Next i

Deleting two rows

Dim startCell As Range, endCell As Range
Set startCell = ActiveCell
Set endCell = startCell.Offset(2, 0)
Range(startCell, endCell).EntireRow.Delete

Highlight the rows with 0 with red color based on data given below

A                                                            B                                       C

TYPE qty Good
apple 4 Good
orange 4 Good
banana 0 Low
mango 3
papaya 0 Low

Private Sub CommandButton1_Click()

Dim i As Integer

For i = 1 To 6

If Range(“B” & i).Value = 0 Then

Range(“B” & i).Offset(0, -1).Resize(, 3).Interior.Color = vbRed

End If
Next i

Leave a Reply