VBA code to select the empty cell below the data

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
737
Office Version
  1. 2019
Platform
  1. Windows
Hello guys
I have a data in sheet Z which needs to be copied and pasted in sheet F in the last empty cell with the help of a code. In this sheet it is row 18, but how do I select the empty cell below the cell which contains data. If you run this code you will understand what I am trying to achieve.
Sub singleEntries()
' singleEntries Macro
Sheets("Z").Select
Range("B2:D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("F").Select
ActiveSheet.Paste
Range("H18").Select
Sheets("Z").Select
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=-3
Sheets("F").Select
ActiveSheet.Paste
Sheets("Z").Select
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("F").Select
Range("G18").Select
ActiveSheet.Paste
Sheets("Z").Select
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("F").Select
Range("I18").Select
ActiveSheet.Paste
Range("F17").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F17:F18")
Range("F17:F18").Select
Selection.AutoFill Destination:=Range("F17:F37")
Range("F17:F37").Select
ActiveWindow.SmallScroll Down:=6
End Sub

Book3
ABCDEFG
1LineDateVch TypeVch No.ParticularsDebitCredit
2602-07-2021Contra101Cash-20002000
3703-07-2021Contra102Cash25000-25000
41907-07-2021Contra105Cash-20002000
52008-07-2021Contra106Cash25000-25000
62712-07-2021Payment108January100-100
73302-07-2021Contra110Cash-20002000
83403-07-2021Contra111Cash25000-25000
94607-07-2021Contra114Cash-20002000
104708-07-2021Contra115Cash25000-25000
115412-07-2021Payment117January100-100
126002-07-2021Contra119Cash-20002000
136103-07-2021Contra120Cash25000-25000
147307-07-2021Contra123Cash-20002000
157408-07-2021Contra124Cash25000-25000
168112-07-2021Payment126January100-100
178702-07-2021Contra128Cash-20002000
188803-07-2021Contra129Cash25000-25000
1910007-07-2021Contra132Cash-20002000
2010108-07-2021Contra133Cash25000-25000
2110812-07-2021Payment135January100-100
Z


Book3
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBG
1HelpDateVoucher TypeVoucher No.NarrationLedger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Total
2Matched30-06-2021Receipt100Bank-4040Sunday1015Monday2025Tuesday1000.5Round Off-0.50.00
3Matched04-07-2021Payment103Bank1001January-100February-200March-300April-400Round Off-10.00
4Matched05-07-2021Receipt104Bank-4040Sunday1015Monday2025Tuesday1000.5Round Off-0.50.00
5Matched09-07-2021Payment107Bank1001January-100February-200March-300April-400Round Off-10.00
6Matched30-06-2021Receipt109Bank-4040Sunday1015Monday2025Tuesday1000.5Round Off-0.50.00
7Matched04-07-2021Payment112Bank1001January-100February-200March-300April-400Round Off-10.00
8Matched05-07-2021Receipt113Bank-4040Sunday1015Monday2025Tuesday1000.5Round Off-0.50.00
9Matched09-07-2021Payment116Bank1001January-100February-200March-300April-400Round Off-10.00
10Matched30-06-2021Receipt118Bank-4040Sunday1015Monday2025Tuesday1000.5Round Off-0.50.00
11Matched04-07-2021Payment121Bank1001January-100February-200March-300April-400Round Off-10.00
12Matched05-07-2021Receipt122Bank-4040Sunday1015Monday2025Tuesday1000.5Round Off-0.50.00
13Matched09-07-2021Payment125Bank1001January-100February-200March-300April-400Round Off-10.00
14Matched30-06-2021Receipt127Bank-4040Sunday1015Monday2025Tuesday1000.5Round Off-0.50.00
15Matched04-07-2021Payment130Bank1001January-100February-200March-300April-400Round Off-10.00
16Matched05-07-2021Receipt131Bank-4040Sunday1015Monday2025Tuesday1000.5Round Off-0.50.00
17Matched09-07-2021Payment134Bank1001January-100February-200March-300April-400Round Off-10.00
18 0000000000000000000 
19 0000000000000000000 
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
F
Cell Formulas
RangeFormula
A2:A37A2=IF(B2&C2&F2&G2&H2&I2="","",IF(OR(B2="",C2="",F2="",G2="",H2="",I2=""),"Debit/Credit Not Matching",IF(BG2=0,"Matched","Debit/Credit Not Matching")))
BG2:BG37BG2=IF(B2="","",ROUND(G2+I2+K2+M2+O2+Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2+AU2+AW2+AY2+BA2+BC2+BE2,2))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2:BE19Expression=AND($O2<>0,ISEVEN(COLUMN(B2)))textNO
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,306
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You could have searched the board as there have been hundreds of last row codes posted, one way if the cells are truly blank is
VBA Code:
Sheets("F").Range("F" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial
or if the cells contain a formula returning "" then try
VBA Code:
Sheets("F").Columns("F").Find("*", , xlValues, , xlByRows, xlPrevious).Offset(1, 0).PasteSpecial
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
737
Office Version
  1. 2019
Platform
  1. Windows
You could have searched the board as there have been hundreds of last row codes posted, one way if the cells are truly blank is
VBA Code:
Sheets("F").Range("F" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial
or if the cells contain a formula returning "" then try
VBA Code:
Sheets("F").Columns("F").Find("*", , xlValues, , xlByRows, xlPrevious).Offset(1, 0).PasteSpecial
Mark858. The columns D E and F are overwritten on the already existing data. It got messed up. The code what I have written works perfectly but when the data is different with more rows it will select the 18th row only and paste which may not be the empty cell. Please note the headings are different but the columns in which they are pasted are correct.
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
737
Office Version
  1. 2019
Platform
  1. Windows
Mark858. The columns D E and F are overwritten on the already existing data. It got messed up. The code what I have written works perfectly but when the data is different with more rows it will select the 18th row only and paste which may not be the empty cell. Please note the headings are different but the columns in which they are pasted are correct.
Please check this code
Option Explicit

Sub singleEntries()
' singleEntries Macro
Sheets("Z").Select
Range("B2:D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("F").Select
ActiveSheet.Paste
Range("H18").Select
Sheets("Z").Select
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=-3
Sheets("F").Select
ActiveSheet.Paste
Sheets("Z").Select
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("F").Select
Range("G18").Select
ActiveSheet.Paste
Sheets("Z").Select
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("F").Select
Range("I18").Select
ActiveSheet.Paste
Range("F17").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F17:F18")
Range("F17:F18").Select
Selection.AutoFill Destination:=Range("F17:F37")
Range("F17:F37").Select
ActiveWindow.SmallScroll Down:=6
End Sub
I am getting this result.
Book3
ABCDEFGHIJKLM
1HelpDateVoucher TypeVoucher No.NarrationLedger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+) Ledger Name Dr(-), Cr(+)
2Matched30-06-2021Receipt100Bank-4040Sunday1015Monday2025Tuesday1000.5
3Matched04-07-2021Payment103Bank1001January-100February-200March-300
4Matched05-07-2021Receipt104Bank-4040Sunday1015Monday2025Tuesday1000.5
5Matched09-07-2021Payment107Bank1001January-100February-200March-300
6Matched30-06-2021Receipt109Bank-4040Sunday1015Monday2025Tuesday1000.5
7Matched04-07-2021Payment112Bank1001January-100February-200March-300
8Matched05-07-2021Receipt113Bank-4040Sunday1015Monday2025Tuesday1000.5
9Matched09-07-2021Payment116Bank1001January-100February-200March-300
10Matched30-06-2021Receipt118Bank-4040Sunday1015Monday2025Tuesday1000.5
11Matched04-07-2021Payment121Bank1001January-100February-200March-300
12Matched05-07-2021Receipt122Bank-4040Sunday1015Monday2025Tuesday1000.5
13Matched09-07-2021Payment125Bank1001January-100February-200March-300
14Matched30-06-2021Receipt127Bank-4040Sunday1015Monday2025Tuesday1000.5
15Matched04-07-2021Payment130Bank1001January-100February-200March-300
16Matched05-07-2021Receipt131Bank-4040Sunday1015Monday2025Tuesday1000.5
17Matched09-07-2021Payment134Bank1001January-100February-200March-300
18Matched02-07-2021Contra101Bank-2000Cash20000
19Matched03-07-2021Contra102Bank25000Cash-250000
20Matched07-07-2021Contra105Bank-2000Cash2000
21Matched08-07-2021Contra106Bank25000Cash-25000
22Matched12-07-2021Payment108Bank100January-100
23Matched02-07-2021Contra110Bank-2000Cash2000
24Matched03-07-2021Contra111Bank25000Cash-25000
25Matched07-07-2021Contra114Bank-2000Cash2000
26Matched08-07-2021Contra115Bank25000Cash-25000
27Matched12-07-2021Payment117Bank100January-100
28Matched02-07-2021Contra119Bank-2000Cash2000
29Matched03-07-2021Contra120Bank25000Cash-25000
30Matched07-07-2021Contra123Bank-2000Cash2000
31Matched08-07-2021Contra124Bank25000Cash-25000
32Matched12-07-2021Payment126Bank100January-100
33Matched02-07-2021Contra128Bank-2000Cash2000
34Matched03-07-2021Contra129Bank25000Cash-25000
35Matched07-07-2021Contra132Bank-2000Cash2000
36Matched08-07-2021Contra133Bank25000Cash-25000
37Matched12-07-2021Payment135Bank100January-100
F
Cell Formulas
RangeFormula
A2:A37A2=IF(B2&C2&F2&G2&H2&I2="","",IF(OR(B2="",C2="",F2="",G2="",H2="",I2=""),"Debit/Credit Not Matching",IF(BG2=0,"Matched","Debit/Credit Not Matching")))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2:BE19Expression=AND($O2<>0,ISEVEN(COLUMN(B2)))textNO
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
737
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I am selecting the range B118:D118, G18, H18 and I18 manually. This is where I need to select the empty cell with the help of a code.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,306
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Replace everywhere in my code lines the F in the range with whatever column you have in your original code i.e,.
The lines
VBA Code:
Sheets("F").Select
Range("I18").Select
ActiveSheet.Paste
becomes either
Rich (BB code):
Sheets("F").Range("I" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial
or
Rich (BB code):
Sheets("F").Columns("I").Find("*", , xlValues, , xlByRows, xlPrevious).Offset(1, 0).PasteSpecial

You need to do this for every instance of a Paste
 
Last edited:

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
737
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Replace everywhere in my code lines the F in the range with whatever column you have in your original code i.e,.
The lines
VBA Code:
Sheets("F").Select
Range("I18").Select
ActiveSheet.Paste
becomes either
VBA Code:
Sheets("[COLOR=rgb(0, 0, 0)]F[/COLOR]").Range("[COLOR=rgb(184, 49, 47)][B]I[/B][/COLOR]" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial
or
VBA Code:
Sheets("F").Columns("[B][COLOR=rgb(184, 49, 47)]I[/COLOR][/B]").Find("*", , xlValues, , xlByRows, xlPrevious).Offset(1, 0).PasteSpecial

You need to do this for every instance of a Paste
Problem solved. Thanks Mark858.😊
Replace everywhere in my code lines the F in the range with whatever column you have in your original code i.e,.
The lines
VBA Code:
Sheets("F").Select
Range("I18").Select
ActiveSheet.Paste
becomes either
VBA Code:
Sheets("[COLOR=rgb(0, 0, 0)]F[/COLOR]").Range("[COLOR=rgb(184, 49, 47)][B]I[/B][/COLOR]" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial
or
VBA Code:
Sheets("F").Columns("[B][COLOR=rgb(184, 49, 47)]I[/COLOR][/B]").Find("*", , xlValues, , xlByRows, xlPrevious).Offset(1, 0).PasteSpecial

You need to do this for every instance of a Paste
Mark858, I am not able to place the code in the right line. Please can you explain it once again.
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
737
Office Version
  1. 2019
Platform
  1. Windows
I am pasting the code for your reference.
Option Explicit

Sub Mark858()
Sheets("Z").Select
Range("B2:D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("F").Select
Range("B18").Select
ActiveSheet.Paste
Sheets("Z").Select
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("F").Select
Range("H18").Select
ActiveSheet.Paste
Sheets("Z").Select
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("F").Select
Range("G18").Select
ActiveSheet.Paste
Sheets("Z").Select
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("F").Select
Range("I18").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-21
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F2:F37")
Range("F2:F37").Select
ActiveWindow.SmallScroll Down:=9
Range("C18").Select
End Sub
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,306
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What cell is your cursor in when you start the code and what cell is the below pasting in?

VBA Code:
Range("B2:D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("F").Select
ActiveSheet.Paste
 

Forum statistics

Threads
1,148,020
Messages
5,744,364
Members
423,864
Latest member
GolfingTitan116

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top