VBA code to select the empty cell below the data

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
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
 
Upvote 0
MARK858 said:
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
Click to expand...
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.
 
Upvote 0
RAJESH1960 said:
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.
Click to expand...
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
MARK858 said:
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
Click to expand...
Problem solved. Thanks Mark858.?
MARK858 said:
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
Click to expand...
Mark858, I am not able to place the code in the right line. Please can you explain it once again.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

Guna13
  • Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
740
Guna13
Guna13
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
156
shknbk2
S
I
  • Solved
If Else advice to allow or forget transfer of values
Replies
7
Views
364
ipbr21054
I
I
  • Solved
Sorting a table range using vba
Replies
2
Views
256
ipbr21054
I
N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
162
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,520
Messages
6,120,011
Members
448,935
Latest member
ijat

Share this page

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
Back
Top